create or replace PACKAGE PK_SMS_MST IS

  -- Author  : HOP.DANG
  -- Created : 2013-01-23 3:00:51 PM
  -- Purpose : 
  
  const_cmdcode_so       CONSTANT    VARCHAR2(4)  := 'LGDK';
  const_cmdcode_wt       CONSTANT    VARCHAR2(4)  := 'LGBH';
  const_cmdcode_isl      CONSTANT    VARCHAR2(4)  := 'LGLD';
  --
  const_sms_ncrt         CONSTANT    VARCHAR2(10) := 'SMS_NCR';
  const_sms_nvl          CONSTANT    VARCHAR2(10) := 'SMS_NVL';
  const_cell_nvl         CONSTANT    VARCHAR2(10) := 'CELL_NVL';
  const_asb_ncrt         CONSTANT    VARCHAR2(10) := 'ASB_NCR';
  
  const_md_nvl           CONSTANT    VARCHAR2(10) := 'MD_NVL';
  const_ac_nvl           CONSTANT    VARCHAR2(10) := 'AC_NVL';
  const_sn_nvl           CONSTANT    VARCHAR2(10) := 'SN_NVL';
   
  const_wt_sn_vl         CONSTANT    VARCHAR2(10) := 'WT_SN_VL';
  const_wt_24_vl         CONSTANT    VARCHAR2(10) := 'WT_S1_VL24';
  const_wt_12_vl         CONSTANT    VARCHAR2(10) := 'WT_S1_VL12';
  const_wt_sn_nvl        CONSTANT    VARCHAR2(10) := 'WT_SN_NVL';
  const_wt_sn_many       CONSTANT    VARCHAR2(10) := 'WT_SN_MNY';
  
  const_so_sn_vl         CONSTANT    VARCHAR2(10) := 'SO_SN_VL';
  const_so_sn_dup        CONSTANT    VARCHAR2(10) := 'SO_SN_DUP';
  const_so_del_dup       CONSTANT    VARCHAR2(10) := 'SO_DEL_DUP';
  const_so_sn_many       CONSTANT    VARCHAR2(10) := 'SO_SN_MNY';
  const_sn_out_date      CONSTANT    VARCHAR2(10) := 'SN_OU_DAT';
  const_asb_sucss        CONSTANT    VARCHAR2(10) := 'ASB_SUS';
  const_inc_amt_m        CONSTANT    VARCHAR2(10) := 'INC_AMT_M';
  
  const_so_sn_inc_dup    CONSTANT    VARCHAR2(20) := 'SN_DUP_INCENTIVE';
  const_sn_isl_dup       CONSTANT    VARCHAR2(20) := 'SN_DUP_INSTALLER';
  
  const_sn_pnd_vl        CONSTANT    VARCHAR2(10) := 'PND_SN_VL';
  const_sn_pnd_dup       CONSTANT    VARCHAR2(10) := 'PND_SN_DUP';
  
  const_sn_isl_vl        CONSTANT    VARCHAR2(10) := 'AC_INSL';
  const_wcl_isl          CONSTANT    VARCHAR2(10) := 'WCL_ISL';
  const_wcl_isl_dt       CONSTANT    VARCHAR2(160):= 'Tich diem Lap dat Dieu hoa LG. De tich diem nhan thuong, sau khi lap dat soan LGLD Serial Model SDTNguoidung gui 8069';
  const_shp_reg_su       CONSTANT    VARCHAR2(10) := 'SHP_REG_SU';
            
  const_wt_2ys           CONSTANT    VARCHAR2(3)  := 'MN';
  const_wt_2yrs          CONSTANT    VARCHAR2(3)  := 'MNT';
  const_wt_wm_2yrs       CONSTANT    VARCHAR2(3)  := 'WM';
  const_wt_rf_2yrs       CONSTANT    VARCHAR2(3)  := 'RF';
  const_wt_tv_2yrs       CONSTANT    VARCHAR2(3)  := 'TV';
  const_wt_ac_2yrs       CONSTANT    VARCHAR2(3)  := 'AC';
  
  const_wt_outdate       CONSTANT    NUMBER(3)    := 209;
     
  --------------------------------------------------------------
  PROCEDURE PR_UPDATE_MO_HIST(
            p_cell_no               VARCHAR2
           ,p_shortcode             VARCHAR2
           ,p_moseq                 VARCHAR2
           ,p_cmdcode               VARCHAR2
           ,p_msgbody               VARCHAR2
           ,p_receive_time          VARCHAR2
           ,p_filename              VARCHAR2
           ,p_filedate              VARCHAR2); 
  --------------------------------------------------------------
  PROCEDURE PR_BUDGE_MO_MT;
  --------------------------------------------------------------
  PROCEDURE PR_BUDGE_PND_MT;
  --------------------------------------------------------------
  PROCEDURE PR_BUDGE_ISL_MT;
  --------------------------------------------------------------
  FUNCTION FN_MAKE_PND_RESPONSE(    
           p_shop_code              VARCHAR2
          ,p_shop_cell              VARCHAR2
          ,p_moseq                  VARCHAR2
          ,p_serial_no              VARCHAR2
          ,p_model                  VARCHAR2
          ,p_enduser_cell           VARCHAR2
          ,p_receive_date           DATE
          ,p_pnd_type               VARCHAR2
          ,p_reject_flag            VARCHAR2)
  RETURN                            MT_RESP_TYPE;
  --------------------------------------------------------------
  FUNCTION FN_MAKE_MT_RESPONSE(  
           p_cell_no                VARCHAR2
          ,p_moseq                  VARCHAR2
          ,p_msgbody                VARCHAR2
          ,p_cmdcode                VARCHAR2
          ,p_receive_date           DATE    ) 
  RETURN                            MT_RESP_TYPE;
  --------------------------------------------------------------
  FUNCTION FN_GET_MT_RESPONSE(   
           p_resp_type              VARCHAR2
          ,p_mt_sub_msg             VARCHAR2)
  RETURN                            VARCHAR2;   
  --------------------------------------------------------------
  FUNCTION FN_GET_WT_RESPONSE(    
           p_serial_no              VARCHAR2
          ,p_model                  VARCHAR2)
  RETURN                            WT_RESP_TYPE;
  --------------------------------------------------------------
  FUNCTION FN_MSGBODY_SIMULATE(
           p_msgbody                VARCHAR2
          ,p_cmdcode                VARCHAR2)
  RETURN                            SMS_INF_TYPE;
  --------------------------------------------------------------
  FUNCTION FN_SN_VALIDATE(    
           p_serial_no              VARCHAR2)
  RETURN                            BOOLEAN; 
  --------------------------------------------------------------
  FUNCTION FN_SN_DUP_CHECK(   
           p_serial_no              VARCHAR2
          ,p_model                  VARCHAR2
          ,p_dup_type               VARCHAR2
          ,p_cmdcode                VARCHAR2) 
  RETURN                            SN_DUP_TYPE;
  --------------------------------------------------------------
  FUNCTION FN_SN_VERIFY(      
           p_serial_no              VARCHAR2
          ,p_model                  VARCHAR2)
  RETURN                            SN_VRF_TYPE;
  --------------------------------------------------------------
  FUNCTION FN_SN_VERIFY_ISL(
           p_serial_no              VARCHAR2
          ,p_model                  VARCHAR2)
  RETURN                            SN_VRF_TYPE; 
  --------------------------------------------------------------
  FUNCTION FN_SN_PROD_CHK(
           p_serial_no              VARCHAR2)
  RETURN                            VARCHAR2;                    
  --------------------------------------------------------------
  FUNCTION FN_SMS_TRANS(
           p_sms                    VARCHAR2)
  RETURN                            VARCHAR2;
  --------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_SO_WT_MST(
            p_shop_code             VARCHAR2              ,p_shop_cell             VARCHAR2 
           ,p_serial_no             VARCHAR2              ,p_model                 VARCHAR2
           ,p_suffix                VARCHAR2              ,p_sellin_date           DATE 
           ,p_sellout_date          DATE                  ,p_sellout_time          VARCHAR2 
           ,p_sellout_resp_type     VARCHAR2              ,p_sellout_resp_msg      VARCHAR2
           ,p_sms_yn                VARCHAR2 DEFAULT 'Y'  ,p_end_user_cell         VARCHAR2
           ,p_incentive_cfm_flag    VARCHAR2 DEFAULT 'N'  ,p_close_flag            VARCHAR2 DEFAULT 'N'); 
  --------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_SO_WT_HIST(
            p_serial_no             VARCHAR2              ,p_resp_type             VARCHAR2
           ,p_resp_msg              VARCHAR2              ,p_mo_msgbody            VARCHAR2
           ,p_receive_date          DATE                  ,p_shop_cell             VARCHAR2 DEFAULT null
           ,p_end_user_cell         VARCHAR2 DEFAULT null ,p_sms_yn                VARCHAR2 DEFAULT 'Y'
           ,p_moseq                 VARCHAR2 DEFAULT 0    ,p_cmdcode               VARCHAR2 DEFAULT const_cmdcode_so
           ,p_model                 VARCHAR2 DEFAULT null ,p_shop_code             VARCHAR2 DEFAULT null
           ,p_success_flag          VARCHAR2 DEFAULT 'N'  ,p_edi_file              VARCHAR2 DEFAULT null);
  --------------------------------------------------------------
  PROCEDURE PR_UPDATE_PND_HIST( 
            p_serial_no              VARCHAR2             ,p_model                 VARCHAR2
           ,p_cmdcode                VARCHAR2             ,p_moseq                 VARCHAR2
           ,p_shop_code              VARCHAR2             ,p_shop_cell             VARCHAR2
           ,p_end_user_cell          VARCHAR2             ,p_pnd_type              VARCHAR2
           ,p_resp_type              VARCHAR2             ,p_resp_msg              VARCHAR2
           ,p_mo_msgbody             VARCHAR2             ,p_receive_time          DATE
           ,p_sms_yn                 VARCHAR2 DEFAULT 'Y'   ,p_cfm_flag            VARCHAR2 DEFAULT 'N'
           ,p_cfm_user               VARCHAR2 DEFAULT 'SYS' ,p_cfm_date            DATE     DEFAULT SYSDATE);  
  ---------------------------------------------------------------                                     
  PROCEDURE PR_UPDATE_ISL_HIST(
            p_serial_no                VARCHAR2, p_model                           VARCHAR2
           ,p_cmdcode                  VARCHAR2, p_moseq                           VARCHAR2
           ,p_isl_code                 VARCHAR2, p_isl_cell                        VARCHAR2
           ,p_end_user_cell            VARCHAR2, p_resp_type                       VARCHAR2
           ,p_resp_msg                 VARCHAR2, p_isl_date                        DATE);                                                                          
END PK_SMS_MST;
/

create or replace PACKAGE BODY PK_SMS_MST IS
    -- M: MANY_ROWS
    -- S: SYNTAX INCORRECT
    -- A: AIR-CON WITHOUT MODEL
    -- F: FALSE
    -- D: DUPPLICATED
    -- O: OK
    
  ------------------------------------------------------------------------------------------
  ---                   PROCEDURE GATHERING MO DATA
  ------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_MO_HIST(
            p_cell_no                     VARCHAR2
           ,p_shortcode                   VARCHAR2
           ,p_moseq                       VARCHAR2
           ,p_cmdcode                     VARCHAR2
           ,p_msgbody                     VARCHAR2
           ,p_receive_time                VARCHAR2
           ,p_filename                    VARCHAR2
           ,p_filedate                    VARCHAR2)
  -------------------------------------------------------------------------------------------------                                           
  IS
  -------------------------------------------------------------------------------------------------
  BEGIN
    INSERT INTO                           TB_MO_HIST( 
                                          cell_no       ,shortcode
                                         ,moseq         ,cmdcode
                                         ,msgbody       ,receive_time
                                         ,filename      ,filedate
                                         ,mo_seq) 
    VALUES                                          (
                                          p_cell_no     ,p_shortcode
                                         ,p_moseq       ,p_cmdcode
                                         ,p_msgbody     ,TO_DATE(p_receive_time,'YYYYMMDDHH24MISS')
                                         ,p_filename    ,p_filedate
                                         ,mo_seq.nextval);
                     
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'PR_BUDGE_MO_MT' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                     ,p_msgbody || ':'     || TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS'));
                                     
      /*dbms_output.put_line ('PR_UPDATE_MO_HIST :' || SQLERRM || '=>' || p_msgbody
                                                  || ':'     || TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS'));
      write_log;       */                                     
  END PR_UPDATE_MO_HIST;
  ------------------------------------------------------------------------------------------
  --                    PROCEDURE BUDGE MO TO MT
  ------------------------------------------------------------------------------------------
  PROCEDURE PR_BUDGE_MO_MT
  ------------------------------------------------------------------------------------------
  IS
    v_msgbody                             VARCHAR2(160);
    v_msgbody_resp                        VARCHAR2(160);                             
    v_cell_no                             VARCHAR2(020);
    v_moseq                               VARCHAR2(020);
    v_cmdcode                             VARCHAR2(050);
    v_resp_type                           VARCHAR2(050);
    v_receive_time                        DATE;
        
    v_mt_response                         MT_RESP_TYPE := MT_RESP_TYPE (null, null, null, null, null, null, null, null);
  ------------------------------------------------------------------------------------------
  BEGIN
    FOR curs_mo IN( 
      SELECT                            cell_no,
                                        shortcode,
                                        moseq,
                                        cmdcode,
                                        msgbody,
                                        receive_time
      FROM                              TB_MO_HIST
      WHERE             transfer_flag = 'N')
    LOOP
      v_msgbody                        := UPPER(LTRIM(RTRIM(curs_mo.msgbody)));
      v_cell_no                        := curs_mo.cell_no;
      v_moseq                          := curs_mo.moseq;
      v_cmdcode                        := UPPER(LTRIM(RTRIM(curs_mo.cmdcode)));
      v_receive_time                   := curs_mo.receive_time;
      --Getting the response by request infos     
      v_mt_response                    := FN_MAKE_MT_RESPONSE( v_cell_no ,v_moseq ,v_msgbody
                                                              ,v_cmdcode ,v_receive_time);
  ------------------------------------------------------------------------------------------
   /*   dbms_output.put_line (v_cmdcode ||'/'|| v_mt_response.so_resp_type ||','|| v_mt_response.mt_return 
                           ||','|| v_mt_response.so_mt_resp_msg );*/
  ------------------------------------------------------------------------------------------
      SELECT DECODE(SUBSTR(v_cmdcode,1,4), const_cmdcode_so
                                         , v_mt_response.so_resp_type
                                         , DECODE(SUBSTR(v_cmdcode,1,4), const_cmdcode_wt
                                                                      , v_mt_response.wt_resp_type
                                                                      , v_mt_response.isl_resp_type))
      INTO   v_resp_type
      FROM   dual;
      
      SELECT DECODE(SUBSTR(v_cmdcode,1,4), const_cmdcode_so
                                         , v_mt_response.so_mt_resp_msg  
                                         , DECODE(SUBSTR(v_cmdcode,1,4), const_cmdcode_wt
                                         , v_mt_response.wt_mt_resp_msg
                                         , v_mt_response.isl_mt_resp_msg))
      INTO   v_msgbody_resp
      FROM   dual;
                                                                                                                                                                                   
      IF (v_mt_response.mt_return  IS NOT NULL) 
        AND (v_mt_response.mt_return   != 'F') THEN
          INSERT INTO        TB_MT_HIST(moseq                         ,cell_no
                                       ,cmdcode                       ,resp_type
                                       ,msgbody                       ,mo_msgbody
                                       ,mo_receive_time               ,mt_seq )
          VALUES                       (v_moseq                       ,v_cell_no
                                       ,SUBSTR(v_cmdcode,1,4)         ,v_resp_type
                                       ,v_msgbody_resp                ,curs_mo.msgbody               
                                       ,v_receive_time                ,mt_seq.nextval);
          COMMIT;
  ------------------------------------------------------------------------------------------
        IF v_mt_response.mt_return      = 'SW' THEN
          INSERT INTO          TB_MT_HIST(moseq                        ,cell_no
                                         ,cmdcode                      ,resp_type
                                         ,msgbody                      ,mo_msgbody
                                         ,mo_receive_time              ,mt_seq )
          VALUES                         ('0'                          ,v_mt_response.wt_end_user_cell
                                         ,const_cmdcode_wt             ,v_mt_response.wt_resp_type
                                         ,v_mt_response.wt_mt_resp_msg ,curs_mo.msgbody
                                         ,v_receive_time               ,mt_seq.nextval);
          COMMIT;
        END IF;  
  ------------------------------------------------------------------------------------------
        UPDATE                            TB_MO_HIST
        SET                transfer_flag = 'Y'
                          ,transfer_date = sysdate
        WHERE              moseq         = v_moseq;
            
        COMMIT;
     
      END IF;
    END LOOP;
        
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'PR_BUDGE_MO_MT' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                     ,v_msgbody);
          
      ROLLBACK;
  END PR_BUDGE_MO_MT;
  ------------------------------------------------------------------------------------------
  --                    PROCEDURE BUDGE PENDING TO MT
  ------------------------------------------------------------------------------------------
  PROCEDURE PR_BUDGE_PND_MT
  IS
    v_pnd_type                       VARCHAR2(10);
    v_serial_no                      VARCHAR2(30);
    v_model                          VARCHAR2(20);
    v_shop_code                      VARCHAR2(10);
    v_shop_cell                      VARCHAR2(20);
    v_moseq                          VARCHAR2(20);
    v_cmdcode                        VARCHAR2(04);
    v_enduser_cell                   VARCHAR2(20);
    v_receive_date                   DATE;
    v_reject_flag                    VARCHAR2(01);    
    v_mt_response                    MT_RESP_TYPE := MT_RESP_TYPE (null, null, null, null, null, null, null, null);
  ------------------------------------------------------------------------------------------
  BEGIN
    FOR curs_pnd IN( 
      SELECT                         serial_no
                                    ,model
                                    ,moseq
                                    ,cmdcode
                                    ,shop_code
                                    ,shop_cell
                                    ,end_user_cell
                                    ,receive_date
                                    ,pnd_type
                                    ,resp_msg
                                    ,reject_flag
      FROM                           TB_SN_PND_HIST
      WHERE           cfm_flag     = 'N')
    LOOP
      v_pnd_type                  := curs_pnd.pnd_type;
      v_serial_no                 := curs_pnd.serial_no;
      v_model                     := curs_pnd.model;
      v_shop_code                 := curs_pnd.shop_code;
      v_shop_cell                 := curs_pnd.shop_cell;
      v_moseq                     := curs_pnd.moseq;
      v_cmdcode                   := curs_pnd.cmdcode;
      v_enduser_cell              := curs_pnd.end_user_cell;
      v_receive_date              := curs_pnd.receive_date;
      v_reject_flag               := curs_pnd.reject_flag;
     -- Getting response by the request infos
      v_mt_response               := FN_MAKE_PND_RESPONSE( v_shop_code    ,v_shop_cell    
                                                          ,v_moseq        ,v_serial_no
                                                          ,v_model        ,v_enduser_cell 
                                                          ,v_receive_date ,v_pnd_type
                                                          ,v_reject_flag);
          
      IF (v_mt_response.mt_return IS NOT NULL) AND (v_mt_response.mt_return != 'F') THEN
        INSERT INTO                  TB_MT_HIST(
                                     moseq
                                    ,cell_no
                                    ,cmdcode
                                    ,resp_type
                                    ,msgbody
                                    ,mo_msgbody
                                    ,mo_receive_time
                                    ,mt_seq)
        VALUES                      (
                                     v_moseq
                                   --  ,'8069'
                                    ,DECODE(v_cmdcode, const_cmdcode_so
                                           ,v_shop_cell, v_enduser_cell)
                                    ,v_cmdcode
                                    ,DECODE(v_cmdcode, const_cmdcode_so
                                           ,v_mt_response.so_resp_type
                                           ,v_mt_response.wt_resp_type)
                                    ,DECODE(v_cmdcode, const_cmdcode_so
                                           ,v_mt_response.so_mt_resp_msg
                                           ,v_mt_response.wt_mt_resp_msg)
                                    ,curs_pnd.resp_msg
                                    ,v_receive_date
                                    ,mt_seq.nextval);
        COMMIT;
            
        IF v_mt_response.mt_return = 'SW' THEN
          INSERT INTO                TB_MT_HIST(
                                     moseq                        ,cell_no
                                    ,cmdcode                      ,resp_type
                                    ,msgbody                      ,mo_msgbody
                                    ,mo_receive_time              ,mt_seq)
          VALUES                    (
                                     '0'                          ,v_mt_response.wt_end_user_cell
                                    ,const_cmdcode_wt             ,v_mt_response.wt_resp_type   
                                    ,v_mt_response.wt_mt_resp_msg ,curs_pnd.resp_msg            
                                    ,v_receive_date               ,mt_seq.nextval);
          COMMIT;
        END IF; 
      END IF;                                       
    END LOOP;
  ------------------------------------------------------------------------------------------                
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'PR_BUDGE_PND_MT' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                      ,v_serial_no || v_model);
      /*dbms_output.put_line ('PR_BUDGE_PND_MT :' || SQLERRM || '=>' || v_serial_no || v_model
                                                 || ':'     || TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS'));
      write_log;*/
         
      ROLLBACK;
  END PR_BUDGE_PND_MT;    
  ------------------------------------------------------------------------------------------
  --                    PROCEDURE BUDGE INSTALLER TO MT
  ------------------------------------------------------------------------------------------
  PROCEDURE PR_BUDGE_ISL_MT
  IS
    v_isl_cell                       VARCHAR2(020);
    v_resp_msg                       VARCHAR2(160);
    v_resp_type                      VARCHAR2(010);
    v_receive_time                   DATE;                           
  BEGIN
    FOR c1 IN (SELECT  i.sms_cell#
                      ,i.create_date
                      ,inst_code                               
               FROM    tb_cm_ac_installer_inf i
               WHERE   sms_yn  = 'N')
    LOOP
      v_receive_time := c1.create_date;
      v_isl_cell     := c1.sms_cell#;
      
      v_resp_type    := const_wcl_isl;
      v_resp_msg     := FN_GET_MT_RESPONSE (v_resp_type,'$' || const_wcl_isl_dt);
       
      INSERT INTO                TB_MT_HIST(
                                 moseq                        ,cell_no
                                ,cmdcode                      ,resp_type
                                ,msgbody                      ,mo_msgbody
                                ,mo_receive_time              ,mt_seq)
      VALUES                    (
                                 '0'                          ,v_isl_cell
                                ,const_cmdcode_isl            ,v_resp_type   
                                ,v_resp_msg                   ,null            
                                ,v_receive_time               ,mt_seq.nextval);
      
      UPDATE tb_cm_ac_installer_inf
      SET    sms_yn     = 'Y'
      WHERE  inst_code  = c1.inst_code;
      
      COMMIT;                                   
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'PR_BUDGE_ISL_MT' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                      ,v_isl_cell);
      ROLLBACK;                    
  END       PR_BUDGE_ISL_MT;     
  ------------------------------------------------------------------------------------------
  --                    MAJOR FUNCTION : MAKE RESPONSE FOR PENDING PRODUCT
  ------------------------------------------------------------------------------------------
  FUNCTION FN_MAKE_PND_RESPONSE(
           p_shop_code                    VARCHAR2
          ,p_shop_cell                    VARCHAR2
          ,p_moseq                        VARCHAR2
          ,p_serial_no                    VARCHAR2
          ,p_model                        VARCHAR2
          ,p_enduser_cell                 VARCHAR2
          ,p_receive_date                 DATE
          ,p_pnd_type                     VARCHAR2
          ,p_reject_flag                  VARCHAR2)
  RETURN                                  MT_RESP_TYPE
  ------------------------------------------------------------------------------------------
  IS
    v_sn_vrf                              SN_VRF_TYPE  := SN_VRF_TYPE(null, null, null, null);
    v_temp                                VARCHAR2(160):= null;
    v_temp_wt                             WT_RESP_TYPE := WT_RESP_TYPE(null, null, null);
                      
    v_dup_ck                              SN_DUP_TYPE  := SN_DUP_TYPE (null, null, null);
    v_return                              MT_RESP_TYPE := MT_RESP_TYPE(null, null, null, null, null, null, null, null);
  ------------------------------------------------------------------------------------------
  BEGIN 
    IF (FN_SN_VALIDATE(p_serial_no)     = FALSE) OR (SUBSTR(p_serial_no,8,1)  = 'O') OR p_reject_flag='Y' THEN
      IF p_pnd_type                     = const_sn_pnd_vl THEN
        v_return.so_resp_type          := const_sms_nvl;
        v_return.so_mt_resp_msg        := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || p_serial_no);
        v_return.mt_return             := 'SO';
        --Update history
         PR_UPDATE_SN_SO_WT_HIST         (p_serial_no           ,v_return.so_resp_type 
                                         ,v_return.so_mt_resp_msg ,v_return.so_mt_resp_msg 
                                         ,p_receive_date        ,p_shop_cell
                                         ,p_enduser_cell        ,'Y'                   
                                         ,p_moseq               ,const_cmdcode_so        
                                         ,v_sn_vrf.model        ,p_shop_code);   
        --Warranty
      ELSE
        v_return.wt_resp_type          := const_sms_nvl;
        v_return.wt_mt_resp_msg        := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || p_serial_no);
        v_return.mt_return             := 'WT';
        --Update history
         PR_UPDATE_SN_SO_WT_HIST         (p_serial_no           ,v_return.wt_resp_type 
                                         ,v_return.so_mt_resp_msg ,v_return.wt_mt_resp_msg 
                                         ,p_receive_date        ,p_shop_cell
                                         ,p_enduser_cell        ,'Y'                   
                                         ,p_moseq               ,const_cmdcode_so        
                                         ,v_sn_vrf.model        ,p_shop_code);  
      END IF;
      --Update pending status 'Y' 
      UPDATE                              TB_SN_PND_HIST
      SET    cfm_flag                   = 'Y'
      WHERE serial_no                   = p_serial_no
      AND   'ABN'||model             LIKE DECODE(p_model, null, '%', '%'|| p_model)
      AND   pnd_type                    = p_pnd_type
      AND   moseq                       = p_moseq;
      
      COMMIT;
      
      RETURN                              v_return;
    END IF;
    
    IF p_pnd_type                       = const_sn_pnd_vl THEN
      --Checking the duplication
      v_dup_ck                         := FN_SN_DUP_CHECK(p_serial_no         ,p_model 
                                                         ,const_so_sn_inc_dup ,const_cmdcode_so);
      IF v_dup_ck.model                IS NULL THEN
        v_dup_ck.model                 := p_model;
      END IF;
             
      CASE
        --Product is duplicated
        WHEN v_dup_ck.dup_flag          = 'D' THEN
          v_return.so_resp_type        := const_so_sn_dup;
          v_return.so_mt_resp_msg      := FN_GET_MT_RESPONSE( v_return.so_resp_type ,v_dup_ck.resp_msg);
          v_return.mt_return           := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST       ( p_serial_no             ,v_return.so_resp_type 
                                         ,v_return.so_mt_resp_msg ,'PND_RESP' 
                                         ,p_receive_date          ,p_shop_cell
                                         ,p_enduser_cell          ,'Y'                   
                                         ,p_moseq                 ,const_cmdcode_so 
                                         ,v_dup_ck.model          ,p_shop_code);                                                
          --Reponse WT period
          /*IF p_enduser_cell         IS NOT NULL THEN
            v_return.wt_resp_type   := const_wt_sn_vl;
            v_return.wt_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,FN_GET_WT_RESPONSE( p_serial_no ,v_dup_ck.model));
            v_return.wt_end_user_cell := p_enduser_cell;
            v_return.mt_return        := 'SW';
                 
            --Update history
            PR_UPDATE_SN_SO_WT_HIST  ( p_serial_no             ,v_return.wt_resp_type 
                                      ,v_return.wt_mt_resp_msg ,'PND_RESP' 
                                      ,p_receive_date          ,p_shop_cell   
                                      ,p_enduser_cell          ,'Y'                     
                                      ,p_moseq                 ,const_cmdcode_wt
                                      ,v_dup_ck.model          ,p_shop_code              
                                      ,'Y');
          END IF;*/
          
          --Update pending status 'Y'                                            
          UPDATE                          TB_SN_PND_HIST
          SET    cfm_flag               = 'Y'
          WHERE serial_no               = p_serial_no
          AND   'ABN'||model         LIKE DECODE(p_model, null, '%', '%'|| p_model)
          AND   pnd_type                = p_pnd_type
          AND   moseq                   = p_moseq;
          
          COMMIT;
          
          RETURN                          v_return;
        --CASE: Product is air-con (should be registered with model)
        WHEN v_dup_ck.DUP_FLAG          = 'M' THEN
          v_return.so_resp_type        := const_so_sn_many;
          v_return.so_mt_resp_msg      := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || p_serial_no); 
          v_return.mt_return           := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST       ( p_serial_no             ,v_return.so_resp_type 
                                         ,v_return.so_mt_resp_msg ,'PND_RESP' 
                                         ,p_receive_date          ,p_shop_cell   
                                         ,p_enduser_cell          ,'Y'                     
                                         ,p_moseq                 ,const_cmdcode_so
                                         ,v_dup_ck.model          ,p_shop_code);
          
          --Update pending status 'Y'                         
          UPDATE                          TB_SN_PND_HIST
          SET    cfm_flag               = 'Y'
          WHERE serial_no               = p_serial_no
          AND   'ABN'||model         LIKE DECODE(p_model, null, '%', '%'|| p_model)
          AND   pnd_type                = p_pnd_type
          AND   moseq                   = p_moseq;
          
          COMMIT;
          
          RETURN                          v_return; 
        --CASE: Duplication check is passed
        ELSE
          NULL;                                                
      END CASE; 
      
      --Verifying the registered information (SN#/Model)
      v_sn_vrf                         := FN_SN_VERIFY( p_serial_no ,p_model);
     
      IF v_sn_vrf.vrf_flag              = 'O' THEN
        v_return.so_resp_type          := const_so_sn_vl;
        v_return.so_mt_resp_msg        := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_sn_vrf.model || ', Serial ' || p_serial_no);
        v_return.mt_return             := 'SO';
             
        PR_UPDATE_SN_SO_WT_MST          ( p_shop_code           ,p_shop_cell    
                                         ,p_serial_no           ,v_sn_vrf.model        
                                         ,v_sn_vrf.suffix       ,v_sn_vrf.sellin_date 
                                         ,p_receive_date        ,TO_CHAR(p_receive_date,'HH24MISS') 
                                         ,v_return.so_resp_type ,v_return.so_mt_resp_msg
                                         ,'Y'                   ,p_enduser_cell);
        --Update history
        PR_UPDATE_SN_SO_WT_HIST         ( p_serial_no           ,v_return.so_resp_type 
                                         ,v_return.so_mt_resp_msg ,v_return.so_mt_resp_msg 
                                         ,p_receive_date        ,p_shop_cell
                                         ,p_enduser_cell        ,'Y'                   
                                         ,p_moseq               ,const_cmdcode_so        
                                         ,v_sn_vrf.model        ,p_shop_code);                        
            
        UPDATE                            TB_SN_RDC_HIST
        SET    sellout_status           = 'Y'
              ,sellout_date             = p_receive_date
        WHERE  serial_no                = p_serial_no
        AND    model                    = v_sn_vrf.model 
        AND    suffix                   = v_sn_vrf.suffix
        AND    out_date                 =(SELECT max(out_date)
                                          FROM   tb_sn_rdc_hist
                                          WHERE  serial_no = p_serial_no
                                          AND    model     = v_sn_vrf.model
                                          AND    suffix    = v_sn_vrf.suffix);
            
        UPDATE tb_sn_cdc_hist
        SET    sellout_flag             = 'Y'
              ,sellout_date             = p_receive_date
        WHERE  serial_no                = p_serial_no
        AND    model                    = v_sn_vrf.model
        AND    suffix                   = v_sn_vrf.suffix;
            
        COMMIT;
            
        IF p_enduser_cell              IS NOT NULL THEN
          v_temp_wt                    := FN_GET_WT_RESPONSE( p_serial_no ,p_model);
          IF v_temp_wt.wt_resp_flag    = 'O' THEN
            IF v_temp_wt.wt_resp_year  = 1 THEN
              v_return.wt_resp_type        := const_wt_12_vl;
            ELSIF v_temp_wt.wt_resp_year = 2 THEN
              v_return.wt_resp_type        := const_wt_24_vl; 
            END IF;  
          END IF;  
          v_return.wt_mt_resp_msg      := FN_GET_MT_RESPONSE( v_return.wt_resp_type, v_temp_wt.wt_resp_msg);
          v_return.wt_end_user_cell    := p_enduser_cell;
          v_return.mt_return           := 'SW';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST       ( p_serial_no           ,v_return.wt_resp_type 
                                         ,v_return.wt_mt_resp_msg ,v_return.wt_mt_resp_msg 
                                         ,p_receive_date        ,p_shop_cell
                                         ,p_enduser_cell        ,'Y'                   
                                         ,p_moseq               ,const_cmdcode_wt        
                                         ,v_sn_vrf.model        ,p_shop_code); 
        END IF;
            
        UPDATE                            TB_SN_PND_HIST
        SET    cfm_flag                 = 'Y'
              ,cfm_date                 = sysdate
              ,cfm_user                 = 'SYS'
        WHERE serial_no                 = p_serial_no
        AND   'ABN'||model           LIKE DECODE(p_model, null, '%', '%'|| p_model)
        AND   pnd_type                  = p_pnd_type
        AND   moseq                     = p_moseq;
      END IF;
          
      RETURN                              v_return;                            
    ELSE
      --CASE : WT PENDING
      v_temp_wt                        := FN_GET_WT_RESPONSE( p_serial_no ,p_model );
      v_return.mt_return               := 'WT';
        
      IF (v_temp_wt.wt_resp_flag       IS NOT NULL) AND  ( v_temp_wt.wt_resp_flag NOT IN ('M','F')) THEN
        IF v_temp_wt.wt_resp_year = 1 THEN
          v_return.wt_resp_type          := const_wt_12_vl;
        ELSIF v_temp_wt.wt_resp_year = 2 THEN
          v_return.wt_resp_type          := const_wt_24_vl;    
        END IF;  
        
        v_return.wt_mt_resp_msg        := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,v_temp_wt.wt_resp_msg);
        
        UPDATE                              TB_SN_PND_HIST
        SET    cfm_flag                   = 'Y'
        WHERE  serial_no                  = p_serial_no
        AND    'ABN'||model            LIKE DECODE(p_model, null, '%', '%'|| p_model)
        AND    pnd_type                   = p_pnd_type
        AND    moseq                      = p_moseq;
        
        PR_UPDATE_SN_SO_WT_HIST         ( p_serial_no           ,v_return.wt_resp_type 
                                         ,v_return.wt_mt_resp_msg ,v_return.wt_mt_resp_msg 
                                         ,p_receive_date        ,p_shop_cell
                                         ,p_enduser_cell        ,'Y'                   
                                         ,p_moseq               ,const_cmdcode_wt        
                                         ,p_model               ,p_shop_code);                                                     
      ELSIF v_temp                      = 'M' THEN 
        v_return.wt_resp_type          := const_wt_sn_many;
        v_return.wt_mt_resp_msg        := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || p_serial_no);   
        
        PR_UPDATE_SN_SO_WT_HIST         ( p_serial_no           ,v_return.wt_resp_type 
                                         ,v_return.wt_mt_resp_msg ,v_return.wt_mt_resp_msg 
                                         ,p_receive_date        ,p_shop_cell
                                         ,p_enduser_cell        ,'Y'                   
                                         ,p_moseq               ,const_cmdcode_wt        
                                         ,p_model               ,p_shop_code); 
      ELSE
        /*PR_RAISE_ERR( 'FN_MAKE_PND_RESPONSE-WT' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                                ,p_serial_no || p_model);*/
        
        v_return.mt_return             := 'F';
      END IF;
       
      RETURN                              v_return;
    END IF;  
      
    RETURN                                v_return;
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'FN_MAKE_PND_RESPONSE' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                           ,p_serial_no || p_model);
          
      v_return.mt_return              := 'F';
      RETURN                             v_return;
  END FN_MAKE_PND_RESPONSE;                                 
  ------------------------------------------------------------------------------------------
  --                    MAJOR FUNCTION : MAKE MT RESPONSE FOR MO
  ------------------------------------------------------------------------------------------
  FUNCTION FN_MAKE_MT_RESPONSE(
           p_cell_no                   VARCHAR2
          ,p_moseq                     VARCHAR2
          ,p_msgbody                   VARCHAR2
          ,p_cmdcode                   VARCHAR2
          ,p_receive_date              DATE    ) 
  RETURN                               MT_RESP_TYPE
  ------------------------------------------------------------------------------------------
  IS
    v_serial_no                        VARCHAR2(30)  := null;
    v_model                            VARCHAR2(20)  := null;
    v_end_user_cell                    VARCHAR2(20)  := null;
    v_shop_code                        VARCHAR2(20)  := null;
    v_shop_cell                        VARCHAR2(20)  := null;
    v_isl_code                         VARCHAR2(20)  := null;
    v_isl_cell                         VARCHAR2(20)  := null;
    v_temp                             VARCHAR2(160) := null;
    v_temp_wt                          WT_RESP_TYPE  := WT_RESP_TYPE(null, null, null);
    v_dup_ck                           SN_DUP_TYPE   := SN_DUP_TYPE (null, null, null);
    v_sn_vrf                           SN_VRF_TYPE   := SN_VRF_TYPE (null, null, null, null);
    v_sn_inf                           SMS_INF_TYPE  := SMS_INF_TYPE(null, null, null, null);
        
    v_return                           MT_RESP_TYPE  := MT_RESP_TYPE(null, null, null, null, null, null, null, null);
  ------------------------------------------------------------------------------------------
  BEGIN
  ---------------------- VALIDATING SYNTAX OF RECEIVED SMS----------------------------------  
    IF (LENGTH(p_cmdcode)          != 4)      
      OR (p_msgbody                IN (const_cmdcode_so, const_cmdcode_wt, const_cmdcode_isl)) THEN
        v_temp                     := SUBSTR(p_cmdcode,1,4); 
      --Sell-out
        IF v_temp                   = const_cmdcode_so THEN
          v_return.so_resp_type    := const_sms_ncrt;
          v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE(v_return.so_resp_type ,'$' || v_temp);
          v_return.mt_return       := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ('NA'                    ,v_return.so_resp_type 
                                      ,v_return.so_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,p_cell_no               
                                      ,'NA'                    ,'Y'
                                      ,p_moseq                 ,v_temp);
        --Warranty
        ELSIF v_temp                  = const_cmdcode_wt THEN
          v_return.wt_resp_type      := const_sms_ncrt;
          v_return.wt_mt_resp_msg    := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_temp);
          v_return.mt_return         := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ( 'NA'                    ,v_return.wt_resp_type   
                                       ,v_return.wt_mt_resp_msg ,p_msgbody 
                                       ,p_receive_date          ,null 
                                       ,p_cell_no               ,'Y'
                                       ,p_moseq                 ,v_temp);
        ELSE 
          v_return.isl_resp_type    := const_sms_ncrt;
         -- v_temp                   := v_temp || '_Serial_Model(IN-DOOR)'; 
          v_return.isl_mt_resp_msg  := FN_GET_MT_RESPONSE(v_return.isl_resp_type ,'$' || v_temp || ' Serial Model(IN-DOOR) DTKH');
          v_return.mt_return       := 'ISL';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ('NA'                    ,v_return.isl_resp_type 
                                      ,v_return.isl_mt_resp_msg,p_msgbody 
                                      ,p_receive_date          ,p_cell_no               
                                      ,'NA'                    ,'Y'
                                      ,p_moseq                 ,v_temp);      
        END IF;
                                   
        RETURN                          v_return;
    END IF;
    
    IF (LENGTH(p_msgbody) - LENGTH(REPLACE(p_msgbody,'   ','')) >0) 
      OR (LENGTH(p_msgbody) - LENGTH(REPLACE(p_msgbody,'    ','')) >0) THEN
        IF p_cmdcode                  = const_cmdcode_so THEN
          v_return.so_resp_type      := const_sms_ncrt;
          v_return.so_mt_resp_msg    := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || p_msgbody);
          v_return.mt_return         := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ( 'NA'                    ,v_return.so_resp_type 
                                       ,v_return.so_mt_resp_msg ,p_msgbody 
                                       ,p_receive_date          ,p_cell_no               
                                       ,'NA'                    ,'Y'
                                       ,p_moseq                 ,v_temp);
        --Warranty
        ELSIF p_cmdcode               = const_cmdcode_wt THEN 
          v_return.wt_resp_type      := const_sms_ncrt;
          v_return.wt_mt_resp_msg    := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || p_msgbody);
          v_return.mt_return         := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ( 'NA'                    ,v_return.wt_resp_type   
                                       ,v_return.wt_mt_resp_msg ,p_msgbody 
                                       ,p_receive_date          ,null 
                                       ,p_cell_no               ,'Y'
                                       ,p_moseq                 ,v_temp);
        ELSE
          v_return.isl_resp_type      := const_sms_ncrt;
          v_return.isl_mt_resp_msg    := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || p_msgbody);
          v_return.mt_return         := 'ISL';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ( 'NA'                    ,v_return.isl_resp_type 
                                       ,v_return.isl_mt_resp_msg,p_msgbody 
                                       ,p_receive_date          ,p_cell_no               
                                       ,'NA'                    ,'Y'
                                       ,p_moseq                 ,v_temp);
        END IF;
                                   
        RETURN                          v_return;
    END IF;  
  ------------------------ SIMULATING THE RECEIVED SMS--------------------------------------
    v_sn_inf  := FN_MSGBODY_SIMULATE(p_msgbody ,p_cmdcode);
        
    v_serial_no                    := v_sn_inf.serial_no;
    v_model                        := v_sn_inf.model;
    v_end_user_cell                := v_sn_inf.end_user_cell;
    
    IF p_cmdcode                    = const_cmdcode_isl AND v_model IS NULL THEN
      v_return.isl_resp_type       := const_sms_ncrt;
     -- v_temp                   := v_temp || '_Serial_Model(IN-DOOR)'; 
      v_return.isl_mt_resp_msg     := FN_GET_MT_RESPONSE(v_return.isl_resp_type ,'$' || p_cmdcode || ' Serial Model(IN-DOOR) DTKH');
      v_return.mt_return           := 'ISL';
      --Update history
      PR_UPDATE_SN_SO_WT_HIST     ('NA'                    ,v_return.isl_resp_type 
                                  ,v_return.isl_mt_resp_msg,p_msgbody 
                                  ,p_receive_date          ,p_cell_no               
                                  ,'NA'                    ,'Y'
                                  ,p_moseq                 ,v_temp); 
      
       RETURN                      v_return;                            
    END IF;  
      
    IF FN_SN_VALIDATE(v_serial_no) = FALSE THEN
      IF p_cmdcode                = const_cmdcode_so THEN
        v_return.so_resp_type    := const_sms_nvl;
        v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no);
        v_return.mt_return       := 'SO';
        --Update history
        PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                   ,v_return.so_mt_resp_msg ,p_msgbody 
                                   ,p_receive_date          ,p_cell_no   
                                   ,v_end_user_cell         ,'Y'                     
                                   ,p_moseq                 ,p_cmdcode      
                                   ,v_model);
          
        /*IF v_end_user_cell          IS NOT NULL THEN
          v_return.wt_resp_type    := const_sms_nvl;
          v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
          v_return.wt_end_user_cell := v_end_user_cell;
          v_return.mt_return        := 'SW';
                   
          --Update history
          PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.wt_resp_type 
                                    ,v_return.wt_mt_resp_msg ,p_msgbody 
                                    ,p_receive_date          ,p_cell_no   
                                    ,v_end_user_cell         ,'Y'                     
                                    ,p_moseq                 ,const_cmdcode_wt
                                    ,v_dup_ck.model          ,v_shop_code              
                                    ,'Y');
        END IF;*/
        --Warranty
      ELSIF p_cmdcode             = const_cmdcode_wt THEN  
        v_return.wt_resp_type    := const_sms_nvl;
        v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
        v_return.mt_return       := 'WT';
        --Update history
        PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                   ,v_return.wt_mt_resp_msg ,p_msgbody 
                                   ,p_receive_date          ,null   
                                   ,p_cell_no               ,'Y'                     
                                   ,p_moseq                 ,const_cmdcode_wt      
                                   ,v_model);
      ELSE
        v_return.isl_resp_type    := const_sms_nvl;
        v_return.isl_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || v_serial_no);
        v_return.mt_return       := 'ISL';
        --Update history
        PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.isl_resp_type 
                                   ,v_return.isl_mt_resp_msg,p_msgbody 
                                   ,p_receive_date          ,p_cell_no   
                                   ,v_end_user_cell         ,'Y'                     
                                   ,p_moseq                 ,p_cmdcode      
                                   ,v_model);      
      END IF;    
            
      RETURN v_return;
    END IF; 
     
    -- Checking product date (Limitation is less than 201209) 
    IF SUBSTR(v_serial_no,1,2) = 'LG' THEN 
      NULL;
    ELSIF TO_NUMBER(SUBSTR(v_serial_no,1,3)) < const_wt_outdate THEN
      --Sell-out
      IF p_cmdcode                  = const_cmdcode_so THEN
        v_return.so_resp_type      := const_sn_out_date;
        v_return.so_mt_resp_msg    := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no);
        v_return.mt_return         := 'SO';
            
        --Update history
        PR_UPDATE_SN_SO_WT_HIST     ( v_serial_no             ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
        -- Reply a SMS to the end user
        IF v_end_user_cell         IS NOT NULL AND p_cmdcode   = const_cmdcode_so THEN
          v_return.wt_resp_type    := const_sn_out_date;
          v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
          v_return.wt_end_user_cell:= v_end_user_cell;
          v_return.mt_return       := 'SW';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                     ,v_return.wt_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_wt      
                                     ,v_model);
        END IF;
         --Warranty
      ELSIF  p_cmdcode   = const_cmdcode_isl THEN
        v_return.isl_resp_type      := const_sn_out_date;
        v_return.isl_mt_resp_msg    := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || v_serial_no);
        v_return.mt_return         := 'SO';
            
        --Update history
        PR_UPDATE_SN_SO_WT_HIST     ( v_serial_no             ,v_return.isl_resp_type 
                                     ,v_return.isl_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
        -- Reply a SMS to the end user
        /*IF v_end_user_cell         IS NOT NULL AND p_cmdcode   = const_cmdcode_so THEN
          v_return.wt_resp_type    := const_sn_out_date;
          v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
          v_return.wt_end_user_cell:= v_end_user_cell;
          v_return.mt_return       := 'SW';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                     ,v_return.wt_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_wt      
                                     ,v_model);
        END IF;*/
      ELSE
        v_return.wt_resp_type      := const_sn_out_date;
        v_return.wt_mt_resp_msg    := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
        v_return.mt_return         := 'WT';
            
        --Update history
        PR_UPDATE_SN_SO_WT_HIST     ( v_serial_no             ,v_return.wt_resp_type 
                                     ,v_return.wt_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,null   
                                     ,p_cell_no               ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_wt      
                                     ,v_model);                                               
      END IF;
          
      RETURN v_return;                                                                                              
    END IF;  
    --
    CASE
      --CASE: serial# is not correct 
      WHEN v_sn_inf.flag            = 'S' THEN
          --Sell-out
        IF p_cmdcode                = const_cmdcode_so THEN
          v_return.so_resp_type    := const_sms_nvl;
          v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no);
          v_return.mt_return       := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
          
          /*IF v_end_user_cell          IS NOT NULL THEN
            v_return.wt_resp_type    := const_sms_nvl;
            v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
            v_return.wt_end_user_cell := v_end_user_cell;
            v_return.mt_return        := 'SW';
                   
            --Update history
            PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.wt_resp_type 
                                      ,v_return.wt_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,p_cell_no   
                                      ,v_end_user_cell         ,'Y'                     
                                      ,p_moseq                 ,const_cmdcode_wt
                                      ,v_dup_ck.model          ,v_shop_code              
                                      ,'Y');
          END IF;*/
          --Warranty
        ELSIF p_cmdcode             = const_cmdcode_wt THEN  
          v_return.wt_resp_type    := const_sms_nvl;
          v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
          v_return.mt_return       := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                     ,v_return.wt_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,null   
                                     ,p_cell_no               ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_wt      
                                     ,v_model);
        ELSE
          v_return.isl_resp_type    := const_sms_nvl;
          v_return.isl_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || v_serial_no);
          v_return.mt_return       := 'ISL';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.isl_resp_type 
                                     ,v_return.isl_mt_resp_msg,p_msgbody 
                                     ,p_receive_date          ,null   
                                     ,p_cell_no               ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_isl      
                                     ,v_model);
        END IF;    
            
        RETURN v_return;
      --CASE : Model is not correct
      WHEN v_sn_inf.flag            = 'M' THEN
        --Sell-out
        IF p_cmdcode                = const_cmdcode_so THEN
          v_return.so_resp_type    := const_md_nvl;
          v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_model);
          v_return.mt_return       := 'SO';
              
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
        --Warranty
        ELSIF p_cmdcode             = const_cmdcode_wt THEN 
          v_return.wt_resp_type    := const_md_nvl;
          v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_model);
          v_return.mt_return       := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                     ,v_return.wt_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,null   
                                     ,p_cell_no               ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_wt      
                                     ,v_model);
        ELSE
          v_return.isl_resp_type    := const_md_nvl;
          v_return.isl_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || v_model);
          v_return.mt_return       := 'ISL';
              
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.isl_resp_type 
                                     ,v_return.isl_mt_resp_msg,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
        END IF;
                
        RETURN v_return;
      --CASE : Model is not correct(Must be Indoor)
      WHEN v_sn_inf.flag            = 'N' THEN
        --Sell-out
        IF p_cmdcode                = const_cmdcode_so THEN
          v_return.so_resp_type    := const_ac_nvl;
          v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE(v_return.so_resp_type ,'$(Model :' || v_model || ')');
          v_return.mt_return       := 'SO';
              
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
        --Warranty
        ELSIF p_cmdcode             = const_cmdcode_wt THEN 
          v_return.wt_resp_type    := const_ac_nvl;
          v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$(Model :' || v_model || ')');
          v_return.mt_return       := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                     ,v_return.wt_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,null   
                                     ,p_cell_no               ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_wt      
                                     ,v_model);
        ELSE
          v_return.isl_resp_type    := const_ac_nvl;
          v_return.isl_mt_resp_msg  := FN_GET_MT_RESPONSE(v_return.isl_resp_type ,'$(Model :' || v_model || ')');
          v_return.mt_return       := 'ISL';
              
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.isl_resp_type 
                                     ,v_return.isl_mt_resp_msg,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
        END IF;
                
        RETURN v_return;
      --CASE: SMS is not in rules
      WHEN v_sn_inf.flag            = 'F' THEN
        --Sell-out
        IF p_cmdcode                = const_cmdcode_so THEN
          v_return.so_resp_type    := const_sms_ncrt;
          v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || SUBSTR(p_cmdcode,1,4));
          v_return.mt_return       := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
          --Warranty
        ELSIF p_cmdcode             = const_cmdcode_wt THEN
          v_return.wt_resp_type    := const_sms_ncrt;
          v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || SUBSTR(p_cmdcode,1,4));
          v_return.mt_return       := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                     ,v_return.wt_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,null   
                                     ,p_cell_no               ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_wt      
                                     ,v_model);
        ELSE
          v_return.isl_resp_type    := const_sms_ncrt;
          v_return.isl_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || SUBSTR(p_cmdcode,1,4)|| '_Serial_Model(IN-DOOR)_DT KH') ;
          v_return.mt_return       := 'ISL';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.isl_resp_type 
                                     ,v_return.isl_mt_resp_msg,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
                                     
        END IF;
                                      
        RETURN                        v_return;
      --CASE : Serial# is air-con (Should registered with model)
      WHEN v_sn_inf.flag            = 'A' THEN
        --Sell-out
        IF p_cmdcode                = const_cmdcode_so THEN
          v_return.so_resp_type    := const_so_sn_many;
          v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no); 
          v_return.mt_return       := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);
        --Warranty
        ELSE
          v_return.wt_resp_type    := const_wt_sn_many;
          v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
          v_return.mt_return       := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                     ,v_return.wt_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,null   
                                     ,p_cell_no               ,'Y'                     
                                     ,p_moseq                 ,const_cmdcode_wt      
                                     ,v_model);
        END IF;
        
        RETURN                        v_return;    
      WHEN v_sn_inf.flag            = 'E' THEN
        IF p_cmdcode                = const_cmdcode_so THEN
          v_return.so_resp_type    := const_sms_ncrt;
          v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE(v_return.so_resp_type ,'$' || p_cmdcode);
          v_return.mt_return       := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ('NA'                    ,v_return.so_resp_type 
                                      ,v_return.so_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,p_cell_no               
                                      ,'NA'                    ,'Y'
                                      ,p_moseq                 ,v_temp);
        --Warranty
        ELSIF p_cmdcode               = const_cmdcode_wt THEN
          v_return.wt_resp_type      := const_sms_ncrt;
          v_return.wt_mt_resp_msg    := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || p_cmdcode);
          v_return.mt_return         := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ( 'NA'                    ,v_return.wt_resp_type   
                                       ,v_return.wt_mt_resp_msg ,p_msgbody 
                                       ,p_receive_date          ,null 
                                       ,p_cell_no               ,'Y'
                                       ,p_moseq                 ,v_temp);
        ELSE 
          v_return.isl_resp_type    := const_sms_ncrt;
         -- v_temp                   := v_temp || '_Serial_Model(IN-DOOR)'; 
          v_return.isl_mt_resp_msg  := FN_GET_MT_RESPONSE(v_return.isl_resp_type ,'$' || p_cmdcode || ' Serial Model(IN-DOOR) DTKH');
          v_return.mt_return        := 'ISL'; 
          --Update history
          PR_UPDATE_SN_SO_WT_HIST     ('NA'                    ,v_return.isl_resp_type 
                                      ,v_return.isl_mt_resp_msg,p_msgbody 
                                      ,p_receive_date          ,p_cell_no               
                                      ,'NA'                    ,'Y'
                                      ,p_moseq                 ,v_temp);      
        END IF;
                                   
        RETURN                         v_return;
      ELSE
        NULL;  
    END CASE;  
  ---------------------------- REGISTERING SELL-OUT ----------------------------------------
    IF p_cmdcode                    = const_cmdcode_so THEN
      --Verifying whether the shop is registered or not (by SMS's cell#)
      BEGIN  
        SELECT                        shop_code
                                     ,DECODE(cell_no#1, p_cell_no, cell_no#1, cell_no#2)
                                      shop_cell
        INTO                          v_shop_code
                                     ,v_shop_cell
        FROM                          TB_CM_SHOP_INF
        WHERE   use_flag            = 'Y'
        AND    (cell_no#1           = p_cell_no
        OR      cell_no#2           = p_cell_no);
            
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_return.so_resp_type     := const_cell_nvl;
          v_return.so_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || p_cell_no);
          v_return.mt_return        := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);  
            
          RETURN                      v_return;  
        WHEN TOO_MANY_ROWS THEN
          PR_RAISE_ERR( 'FN_MAKE_MT_RESPONSE' , 'Shop information duplicated ' || '/'|| SQLERRM 
                                          ,p_cell_no ||'/'|| p_msgbody);
            
      END;
      ---------------------------------CHECKING THE DUPLICATION----------------------------------------------
      v_dup_ck                      := FN_SN_DUP_CHECK(v_serial_no         ,v_model 
                                                      ,const_so_sn_inc_dup ,const_cmdcode_so);
      IF v_dup_ck.model             IS NULL THEN
        v_dup_ck.model              := v_model;
      END IF;
             
      CASE
        --Product is duplicated
        WHEN v_dup_ck.dup_flag       = 'D' THEN
          v_return.so_resp_type     := const_so_sn_dup;
          v_return.so_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.so_resp_type ,v_dup_ck.resp_msg);
          v_return.mt_return        := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg ,p_msgbody 
                                     ,p_receive_date          ,p_cell_no
                                     ,v_end_user_cell         ,'Y'                   
                                     ,p_moseq                 ,p_cmdcode 
                                     ,v_dup_ck.model          ,v_shop_code);                                                
          --Reponse WT period
          /*IF v_end_user_cell        IS NOT NULL THEN
            v_return.wt_resp_type   := const_wt_sn_vl;
            v_return.wt_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,FN_GET_WT_RESPONSE( v_serial_no ,v_dup_ck.model));
            v_return.wt_end_user_cell := v_end_user_cell;
            v_return.mt_return        := 'SW';
                 
            --Update history
            PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.wt_resp_type 
                                      ,v_return.wt_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,p_cell_no   
                                      ,v_end_user_cell         ,'Y'                     
                                      ,p_moseq                 ,p_cmdcode
                                      ,v_dup_ck.model          ,v_shop_code              
                                      ,'Y');
          END IF;*/
                                                     
          RETURN                       v_return;
        --CASE: Product is air-con (should be registered with model)
        WHEN v_dup_ck.DUP_FLAG       = 'M' THEN
          v_return.so_resp_type     := const_so_sn_many;
          v_return.so_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no); 
          v_return.mt_return        := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST    ( v_serial_no             ,v_return.so_resp_type 
                                      ,v_return.so_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,p_cell_no   
                                      ,v_end_user_cell         ,'Y'                     
                                      ,p_moseq                 ,p_cmdcode
                                      ,v_dup_ck.model          ,v_shop_code);
                                  
          RETURN                       v_return; 
        --CASE: Duplication check is passed
        ELSE
          NULL;                                                
      END CASE; 
      -------------------------VERIFYING THE PRODUCT IN VN STORE-----------------------------------------
          
      v_sn_vrf                      := FN_SN_VERIFY( v_serial_no ,v_model);
          
      CASE 
        --CASE: verification is passed
        WHEN v_sn_vrf.vrf_flag       = 'O' THEN
          v_return.so_resp_type     := const_so_sn_vl;
          v_return.so_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_sn_vrf.model || ', Serial ' || v_serial_no);
          v_return.mt_return        :='SO';
          --Update so/wt master
          PR_UPDATE_SN_SO_WT_MST    ( v_shop_code              ,p_cell_no     
                                     ,v_serial_no              ,v_sn_vrf.model        
                                     ,v_sn_vrf.suffix          ,v_sn_vrf.sellin_date      
                                     ,p_receive_date           ,TO_CHAR(p_receive_date,'HH24MISS') 
                                     ,v_return.so_resp_type    ,v_return.so_mt_resp_msg
                                     ,'Y'                      ,v_end_user_cell);
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no              ,v_return.so_resp_type 
                                     ,v_return.so_mt_resp_msg  ,p_msgbody      
                                     ,p_receive_date           ,p_cell_no 
                                     ,v_end_user_cell          ,'Y'        
                                     ,p_moseq                  ,p_cmdcode               
                                     ,v_sn_vrf.model           ,v_shop_code    
                                     ,'Y');
              
          UPDATE tb_sn_rdc_hist
          SET    sellout_status     = 'Y'
                ,sellout_date       = p_receive_date
          WHERE  serial_no          = v_serial_no
          AND    model              = v_sn_vrf.model
          AND    suffix             = v_sn_vrf.suffix
          AND    out_date           = (SELECT max(out_date)
                                       FROM   tb_sn_rdc_hist
                                       WHERE  serial_no = v_serial_no
                                       AND    model     = v_sn_vrf.model
                                       AND    suffix    = v_sn_vrf.suffix);
              
          UPDATE tb_sn_cdc_hist
          SET    sellout_flag       = 'Y'
                ,sellout_date       = p_receive_date
          WHERE  serial_no          = v_serial_no
          AND    model              = v_sn_vrf.model
          AND    suffix             = v_sn_vrf.suffix;
              
          COMMIT;                            
          --Response for WT
          IF v_end_user_cell        IS NOT NULL THEN
            v_temp_wt               := FN_GET_WT_RESPONSE( v_serial_no ,v_sn_vrf.model);
            IF v_temp_wt.wt_resp_flag = 'O' THEN
              IF v_temp_wt.wt_resp_year = 1 THEN
                v_return.wt_resp_type   := const_wt_12_vl;
              ELSIF v_temp_wt.wt_resp_year = 2 THEN
                v_return.wt_resp_type   := const_wt_24_vl;
              END IF;  
            END IF;
            
            v_return.wt_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.wt_resp_type , v_temp_wt.wt_resp_msg);
            v_return.wt_end_user_cell := v_end_user_cell;
            v_return.mt_return        := 'SW';
            --Update history
            PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.wt_resp_type 
                                      ,v_return.wt_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,p_cell_no 
                                      ,v_end_user_cell         ,'Y'                     
                                      ,p_moseq                 ,const_cmdcode_wt
                                      ,v_sn_vrf.model          ,v_shop_code             
                                      ,'Y');
          END IF;
             
          RETURN                       v_return;                       
        --CASE : Product is air-con (Should be registered with model)
        WHEN v_sn_vrf.vrf_flag       = 'M' THEN
          v_return.so_resp_type     := const_so_sn_many;
          v_return.so_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no);
          v_return.mt_return        := 'SO';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.so_resp_type 
                                    ,v_return.so_mt_resp_msg ,p_msgbody 
                                    ,p_receive_date          ,p_cell_no
                                    ,v_end_user_cell         ,'Y'                   
                                    ,p_moseq                 ,p_cmdcode 
                                    ,v_model                 ,v_shop_code);                                     
          
          RETURN                     v_return;                                                                       
        --CASE : Verification is not passed
        ELSE
          --Validating the serial info
          IF SUBSTR(v_serial_no,8,1)  = 'O' THEN
            v_return.so_resp_type    := const_sms_nvl;
            v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no);
            v_return.mt_return       := 'SO';
            --Update history
            PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.so_resp_type 
                                       ,v_return.so_mt_resp_msg ,p_msgbody 
                                       ,p_receive_date          ,p_cell_no   
                                       ,v_end_user_cell         ,'Y'                     
                                       ,p_moseq                 ,p_cmdcode      
                                       ,v_model);
              
            IF v_end_user_cell          IS NOT NULL THEN
              v_return.wt_resp_type    := const_sms_nvl;
              v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
              v_return.wt_end_user_cell := v_end_user_cell;
              v_return.mt_return        := 'SW';
                       
              --Update history
              PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.wt_resp_type 
                                        ,v_return.wt_mt_resp_msg ,p_msgbody 
                                        ,p_receive_date          ,p_cell_no   
                                        ,v_end_user_cell         ,'Y'                     
                                        ,p_moseq                 ,const_cmdcode_wt
                                        ,v_dup_ck.model          ,v_shop_code              
                                        ,'Y');
            END IF;
                
            RETURN v_return;
          END IF;
          /*IF SUBSTR(v_serial_no,4,1) IN ('V') THEN --(,'I','T')
            v_temp                     := FN_SN_PROD_CHK(v_serial_no);
            
            IF v_temp                  = 'F' THEN
              v_return.so_resp_type    := const_sms_nvl;
              v_return.so_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no);
              v_return.mt_return       := 'SO';
              --Update history
              PR_UPDATE_SN_SO_WT_HIST   (v_serial_no             ,v_return.so_resp_type 
                                        ,v_return.so_mt_resp_msg ,p_msgbody 
                                        ,p_receive_date          ,p_cell_no   
                                        ,v_end_user_cell         ,'Y'                     
                                        ,p_moseq                 ,p_cmdcode      
                                        ,v_model);
              
              IF v_end_user_cell          IS NOT NULL THEN
                v_return.wt_resp_type    := const_sms_nvl;
                v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
                v_return.wt_end_user_cell := v_end_user_cell;
                v_return.mt_return        := 'SW';
                         
                --Update history
                PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.wt_resp_type 
                                          ,v_return.wt_mt_resp_msg ,p_msgbody 
                                          ,p_receive_date          ,p_cell_no   
                                          ,v_end_user_cell         ,'Y'                     
                                          ,p_moseq                 ,const_cmdcode_wt
                                          ,v_dup_ck.model          ,v_shop_code              
                                          ,'Y');
              END IF;
                
              RETURN v_return;
            END IF;       
          END IF;  */
          
          --Checking the pending duplication.   
          v_dup_ck                  := FN_SN_DUP_CHECK( v_serial_no     ,v_model 
                                                       ,const_sn_pnd_vl ,const_cmdcode_so);
          IF v_dup_ck.model         IS NULL THEN
            v_dup_ck.model          := v_model;
          END IF;  
              
          CASE
            WHEN v_dup_ck.DUP_FLAG     = 'D' THEN
              v_return.so_resp_type   := const_sn_pnd_dup;
              v_return.so_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.so_resp_type ,v_dup_ck.resp_msg);
              v_return.mt_return      := 'SO';
            WHEN v_dup_ck.DUP_FLAG     = 'M' THEN
              v_return.so_resp_type   := const_so_sn_many;
              v_return.so_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$' || v_serial_no);
              v_return.mt_return      := 'SO';
            WHEN v_dup_ck.DUP_FLAG     = 'O' THEN
              v_return.so_resp_type   := const_sn_pnd_vl;
              v_return.so_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.so_resp_type ,'$'|| v_serial_no);
              v_return.mt_return      := 'SO';
              --Update PENDING history
              PR_UPDATE_PND_HIST         ( v_serial_no             ,v_dup_ck.model 
                                          ,p_cmdcode               ,p_moseq                 
                                          ,v_shop_code             ,p_cell_no  
                                          ,v_end_user_cell         ,v_return.so_resp_type 
                                          ,v_return.so_resp_type   ,v_return.so_mt_resp_msg     
                                          ,p_msgbody               ,p_receive_date);
            ELSE
              NULL;  
          END CASE;
           --Update history
          PR_UPDATE_SN_SO_WT_HIST    ( v_serial_no             ,v_return.so_resp_type 
                                      ,v_return.so_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,p_cell_no  
                                      ,v_end_user_cell         ,'Y'                   
                                      ,p_moseq                 ,p_cmdcode 
                                      ,v_dup_ck.model          ,v_shop_code);
        RETURN v_return;
      END CASE;
    END IF;
  -----------------------------------REQUEST FOR WARRANTY INFO------------------------------
    IF p_cmdcode                     = const_cmdcode_wt THEN
      v_temp_wt                     := FN_GET_WT_RESPONSE( v_serial_no ,v_model );

      CASE
        --CASE: warranty info is avaialble
        WHEN (v_temp_wt.wt_resp_flag  = 'O') THEN                                                     
          IF v_temp_wt.wt_resp_year = 1 THEN
            v_return.wt_resp_type     := const_wt_12_vl; 
          ELSIF v_temp_wt.wt_resp_year = 2 THEN
            v_return.wt_resp_type     := const_wt_24_vl;    
          END IF;  
          
          v_return.wt_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,v_temp_wt.wt_resp_msg);
          v_return.mt_return        := 'WT';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST    ( v_serial_no             ,v_return.wt_resp_type 
                                      ,v_return.wt_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,null               
                                      ,p_cell_no               ,'Y'                     
                                      ,p_moseq                 ,p_cmdcode               
                                      ,v_model                 ,v_shop_code             
                                      ,'Y');
          
          UPDATE tb_sn_so_wt_mst0
          SET    end_user_cell        = p_cell_no
          WHERE  model                = v_model
          AND    serial_no            = v_serial_no;
          
          COMMIT;
                                   
          RETURN                       v_return;
        --CASE: product is air-con (Should be registered with model)
        WHEN v_temp_wt.wt_resp_flag  = 'M' THEN
          v_return.wt_resp_type     := const_wt_sn_many;
          v_return.wt_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
          v_return.mt_return        := 'WT';
              
          --Update history
          PR_UPDATE_SN_SO_WT_HIST    ( v_serial_no             ,v_return.wt_resp_type 
                                      ,v_return.wt_mt_resp_msg ,p_msgbody 
                                      ,p_receive_date          ,null
                                      ,p_cell_no               ,'Y'                   
                                      ,p_moseq                 ,p_cmdcode 
                                      ,v_model                 ,v_shop_code);
                                     
          RETURN v_return;
        -- CASE : PRODUCT IS NOT AVAILABLE    
        ELSE
          --Validating the serial info before insert to pending table
          IF SUBSTR(v_serial_no,8,1)  = 'O' THEN
            v_return.wt_resp_type    := const_sms_nvl;
            v_return.wt_mt_resp_msg  := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no);
            v_return.mt_return       := 'WT';
            --Update history
            PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.wt_resp_type 
                                       ,v_return.wt_mt_resp_msg ,p_msgbody 
                                       ,p_receive_date          ,null   
                                       ,p_cell_no               ,'Y'                     
                                       ,p_moseq                 ,p_cmdcode      
                                       ,v_model);
                
            RETURN v_return;
          END IF;
          
          v_return.wt_resp_type     := const_wt_sn_nvl;
          v_return.wt_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.wt_resp_type ,'$' || v_serial_no   );
          v_return.mt_return        := 'WT';
              
          v_dup_ck                  := FN_SN_DUP_CHECK( v_serial_no     ,v_model 
                                                       ,const_wt_sn_nvl ,p_cmdcode );
          IF v_dup_ck.model         IS NULL THEN
            v_dup_ck.model          := v_model;
          END IF;
                
          /*IF v_dup_ck.dup_flag       = 'O' THEN
            PR_UPDATE_PND_HIST       ( v_serial_no           ,v_dup_ck.model 
                                      ,p_cmdcode             ,p_moseq 
                                      ,v_shop_code           ,null    
                                      ,p_cell_no             ,v_return.wt_resp_type 
                                      ,v_return.wt_resp_type ,v_return.wt_mt_resp_msg      
                                      ,p_msgbody             ,p_receive_date);

            RETURN                     v_return;
          END IF;*/
      END CASE;
    END IF;
  -----------------------------------REQUEST FOR AC INSTALLER------------------------------
    IF p_cmdcode                    = const_cmdcode_isl THEN
      BEGIN  
        SELECT                        inst_code
                                     ,sms_cell#
        INTO                          v_isl_code
                                     ,v_isl_cell
        FROM                          TB_CM_AC_INSTALLER_INF
        WHERE   use_flag            = 'Y'
        AND    sms_cell#            = p_cell_no;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_return.isl_resp_type   := const_cell_nvl;
          v_return.isl_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || p_cell_no);
          v_return.mt_return       := 'ISL';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no             ,v_return.isl_resp_type 
                                     ,v_return.isl_mt_resp_msg,p_msgbody 
                                     ,p_receive_date          ,p_cell_no   
                                     ,v_end_user_cell         ,'Y'                     
                                     ,p_moseq                 ,p_cmdcode      
                                     ,v_model);  
            
          RETURN                      v_return;  
      END;
    
      v_dup_ck                   := FN_SN_DUP_CHECK(v_serial_no  
                                                   ,v_model
                                                   ,const_sn_isl_dup
                                                   ,const_cmdcode_isl);                
      IF v_dup_ck.DUP_FLAG       = 'D' THEN
         v_return.isl_resp_type     := const_so_sn_dup;
         v_return.isl_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,v_dup_ck.resp_msg);
         v_return.mt_return         := 'ISL';
        --Update history
         PR_UPDATE_SN_SO_WT_HIST   (v_serial_no             ,v_return.isl_resp_type 
                                   ,v_return.isl_mt_resp_msg,p_msgbody 
                                   ,p_receive_date          ,p_cell_no
                                   ,v_end_user_cell         ,'Y'                   
                                   ,p_moseq                 ,p_cmdcode 
                                   ,v_dup_ck.model          ,v_isl_code);                                                
         RETURN                     v_return;
      END IF;          
      
      v_sn_vrf                      := FN_SN_VERIFY_ISL(v_serial_no, v_model);
      CASE 
        --CASE: verification is passed
        WHEN v_sn_vrf.vrf_flag       = 'O' THEN
          v_return.isl_resp_type     := const_sn_isl_vl;
          v_return.isl_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || v_sn_vrf.model || '$' || v_serial_no);
          v_return.mt_return         :='ISL';
          
          PR_UPDATE_ISL_HIST(v_serial_no             , v_model
                            ,p_cmdcode               , p_moseq
                            ,v_isl_code              , v_isl_cell
                            ,v_end_user_cell         , v_return.isl_resp_type
                            ,v_return.isl_mt_resp_msg, p_receive_date);
          --Update history
          PR_UPDATE_SN_SO_WT_HIST   ( v_serial_no              ,v_return.isl_resp_type 
                                     ,v_return.isl_mt_resp_msg ,p_msgbody      
                                     ,p_receive_date           ,p_cell_no 
                                     ,v_end_user_cell          ,'Y'        
                                     ,p_moseq                  ,p_cmdcode               
                                     ,v_sn_vrf.model           ,v_isl_code    
                                     ,'Y');
                                                                   
          v_dup_ck.DUP_FLAG          := null;
          v_dup_ck.MODEL             := null;
          v_dup_ck.RESP_MSG          := null;
          
          v_dup_ck                   := FN_SN_DUP_CHECK(v_serial_no         ,v_model 
                                                       ,const_so_sn_inc_dup ,const_cmdcode_so);
          IF v_dup_ck.model             IS NULL THEN
            v_dup_ck.model              := v_model;
          END IF;
          IF v_dup_ck.DUP_FLAG         = 'O' THEN 
            --Update so/wt master
            PR_UPDATE_SN_SO_WT_MST    ( v_isl_code               ,p_cell_no     
                                       ,v_serial_no              ,v_sn_vrf.model        
                                       ,v_sn_vrf.suffix          ,v_sn_vrf.sellin_date      
                                       ,p_receive_date           ,TO_CHAR(p_receive_date,'HH24MISS') 
                                       ,v_return.isl_resp_type   ,v_return.isl_mt_resp_msg
                                       ,'N'                      ,v_end_user_cell
                                       ,'Y'                      ,'Y');
            --Response for WT
            IF v_end_user_cell        IS NOT NULL THEN
              v_temp_wt               := FN_GET_WT_RESPONSE( v_serial_no ,v_sn_vrf.model);
              
              IF v_temp_wt.wt_resp_flag = 'O' THEN
                IF v_temp_wt.wt_resp_year = 1 THEN
                  v_return.wt_resp_type   := const_wt_12_vl;
                ELSIF v_temp_wt.wt_resp_year = 2 THEN
                  v_return.wt_resp_type   := const_wt_24_vl;
                END IF;  
              END IF;           
              
              v_return.wt_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.wt_resp_type, v_temp_wt.wt_resp_msg);
              v_return.wt_end_user_cell := v_end_user_cell;
              v_return.mt_return        := 'SW';
              --Update history
              PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.wt_resp_type 
                                        ,v_return.wt_mt_resp_msg ,p_msgbody 
                                        ,p_receive_date          ,p_cell_no 
                                        ,v_end_user_cell         ,'Y'                     
                                        ,p_moseq                 ,const_cmdcode_wt
                                        ,v_sn_vrf.model          ,v_isl_code             
                                        ,'Y');
            END IF;
                 
            RETURN                       v_return;
          END IF;                       
        --CASE : Product is air-con (Should be registered with model)
        WHEN v_sn_vrf.vrf_flag       = 'M' THEN
          v_return.isl_resp_type     := const_so_sn_many;
          v_return.isl_mt_resp_msg   := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || v_serial_no);
          v_return.mt_return        := 'ISL';
          --Update history
          PR_UPDATE_SN_SO_WT_HIST  ( v_serial_no             ,v_return.isl_resp_type 
                                    ,v_return.isl_mt_resp_msg,p_msgbody 
                                    ,p_receive_date          ,p_cell_no
                                    ,v_end_user_cell         ,'Y'                   
                                    ,p_moseq                 ,p_cmdcode 
                                    ,v_model                 ,v_shop_code);                                     
            
          RETURN                     v_return;
        ELSE
          v_dup_ck                  := FN_SN_DUP_CHECK( v_serial_no     ,v_model 
                                                       ,const_sn_pnd_vl ,const_cmdcode_isl);
          IF v_dup_ck.model         IS NULL THEN
            v_dup_ck.model          := v_model;
          END IF;  
                
          CASE
            WHEN v_dup_ck.DUP_FLAG     = 'D' THEN
              v_return.isl_resp_type   := const_sn_pnd_dup;
              v_return.isl_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,v_dup_ck.resp_msg);
              v_return.mt_return      := 'ISL';
            WHEN v_dup_ck.DUP_FLAG     = 'M' THEN
              v_return.isl_resp_type   := const_so_sn_many;
              v_return.isl_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$' || v_serial_no);
              v_return.mt_return      := 'ISL';
            WHEN v_dup_ck.DUP_FLAG     = 'O' THEN
              v_return.isl_resp_type   := const_sn_pnd_vl;
              v_return.isl_mt_resp_msg := FN_GET_MT_RESPONSE( v_return.isl_resp_type ,'$'|| v_serial_no);
              v_return.mt_return      := 'ISL';
              --Update PENDING history
              PR_UPDATE_PND_HIST         ( v_serial_no             ,v_dup_ck.model 
                                          ,p_cmdcode               ,p_moseq                 
                                          ,v_isl_code              ,p_cell_no  
                                          ,v_end_user_cell         ,v_return.isl_resp_type 
                                          ,v_return.isl_resp_type  ,v_return.isl_mt_resp_msg     
                                          ,p_msgbody               ,p_receive_date);
            ELSE
              NULL;  
          END CASE;
           --Update history
          PR_UPDATE_SN_SO_WT_HIST    ( v_serial_no             ,v_return.isl_resp_type 
                                      ,v_return.isl_mt_resp_msg,p_msgbody 
                                      ,p_receive_date          ,p_cell_no  
                                      ,v_end_user_cell         ,'Y'                   
                                      ,p_moseq                 ,p_cmdcode 
                                      ,v_dup_ck.model          ,v_isl_code);     
       END CASE;
    END IF;
  RETURN                               v_return;
  ------------------------------------------------------------------------------------------ 
  EXCEPTION
    WHEN VALUE_ERROR THEN
      RETURN v_return;
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'FN_MAKE_MT_RESPONSE' , DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                          ,p_cell_no ||'/'|| p_msgbody);
                                               
      RETURN v_return;
  END FN_MAKE_MT_RESPONSE;
  ------------------------------------------------------------------------------------------
  --                    FUNCTION SIMULATE SERIAL_NO/MODEL/END_USER_CELL BY SMS
  ------------------------------------------------------------------------------------------
  FUNCTION FN_MSGBODY_SIMULATE(
           P_MSGBODY                  VARCHAR2
          ,P_CMDCODE                  VARCHAR2)
  RETURN                              SMS_INF_TYPE  
  ------------------------------------------------------------------------------------------
  IS
    v_serial_no                       VARCHAR2(30) := NULL;
    v_product                         VARCHAR2(10) := NULL;
    v_model                           VARCHAR2(20) := NULL;
    v_end_user_cell                   VARCHAR2(20) := NULL;
    v_spacebar_cnt                    NUMBER(3)    := 0;
    v_msgbody                         VARCHAR2(160):= LTRIM(RTRIM(p_msgbody));
    v_temp                            VARCHAR2(30) := NULL;
        
    v_return                          SMS_INF_TYPE := SMS_INF_TYPE ('T', NULL, NULL, NULL); 
  ------------------------------------------------------------------------------------------
  BEGIN
    IF LENGTH(v_msgbody) - LENGTH(REPLACE(v_msgbody,'  ','')) > 0 THEN
      v_msgbody                    := REPLACE(v_msgbody, '  ', ' ');
    END IF;
      
    v_spacebar_cnt                 := LENGTH(v_msgbody) - LENGTH(REPLACE(v_msgbody,' ',''));
    CASE
      WHEN v_spacebar_cnt           = 1 THEN
        --CASE : only serial_no (so/wt)
        v_serial_no                := SUBSTR(v_msgbody, INSTR(v_msgbody, ' ', - 1) +1 ,LENGTH(v_msgbody)- INSTR(v_msgbody, ' ', - 1));
      WHEN v_spacebar_cnt           = 2 THEN
        --CASE : serial_no, end_user_cell/model (so/wt)
        v_temp                     := SUBSTR(v_msgbody, INSTR(v_msgbody, ' ', - 1) +1 ,LENGTH(v_msgbody)- INSTR(v_msgbody, ' ', - 1));
        v_msgbody                  := RTRIM(REPLACE(v_msgbody, v_temp));
        v_serial_no                := SUBSTR(v_msgbody, INSTR(v_msgbody, ' ', - 1) +1 ,LENGTH(v_msgbody)- INSTR(v_msgbody, ' ', - 1));
       --Checking whether the 2nd info is model or not (if not, its end_user_cell)
        BEGIN
          SELECT                      distinct(model)
          INTO                        v_model
          FROM                        TB_CM_MODEL_CAT
          WHERE        model        = v_temp
          GROUP BY     model;
        EXCEPTION
          WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
            IF p_cmdcode            = const_cmdcode_so THEN
              v_end_user_cell      := v_temp;
             -- v_model              := null;
            ELSE
              v_model              := v_temp;
            END IF;    
        END;
     WHEN v_spacebar_cnt            = 3 THEN
        --CASE : serial_no, model, end_user_cell (only for sell-out)
        v_end_user_cell            := SUBSTR(v_msgbody, INSTR(v_msgbody, ' ', - 1) +1 ,LENGTH(v_msgbody)- INSTR(v_msgbody, ' ', - 1));
        v_msgbody                  := RTRIM(REPLACE(v_msgbody, v_end_user_cell));
        v_model                    := SUBSTR(v_msgbody, INSTR(v_msgbody, ' ', - 1) +1 ,LENGTH(v_msgbody)- INSTR(v_msgbody, ' ', - 1));
        v_msgbody                  := RTRIM(REPLACE(v_msgbody, v_model));                           
        v_serial_no                := SUBSTR(v_msgbody, INSTR(v_msgbody, ' ', - 1) +1 ,LENGTH(v_msgbody)- INSTR(v_msgbody, ' ', - 1));
      ELSE 
        v_return.flag              := 'F';
    END CASE;
  ---------------------------------VALIDATION STEP------------------------------------------ 
    IF v_return.flag               != 'F' THEN
      /*--Validating the serial info
      IF FN_SN_VALIDATE(v_serial_no)  = FALSE THEN
        v_return.flag              := 'S';
      END IF;*/
      --Validating the model info
      IF v_model                  IS  NOT NULL THEN
        BEGIN
          SELECT                      distinct(prod_l1) 
                                     ,mp_model
          INTO                        v_product
                                     ,v_model
          FROM                        TB_CM_MODEL_CAT
          WHERE    mp_model         = v_model
          GROUP BY mp_model
                  ,prod_l1 ;
          --CASE: air-con model & serial is not indoor set 
          IF (v_product             = 'AC') THEN
            IF (SUBSTR(v_model,-1, 1) != 'N') AND (SUBSTR(v_model,3,1)  != 'N') THEN               
               v_return.flag       := 'N';
            END IF;     
          END IF;
          --OR SUBSTR(v_model,3,1)  != 'N'
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
          --  IF LENGTH(TRIM(TRANSLATE(SUBSTR(v_model,1,3),    ' +-.0123456789', ' '))) IS NULL THEN
              v_return.flag          := 'M';
          --  ELSE
          --    v_return.flag          := 'E'; 
          --  END IF;     
          WHEN OTHERS THEN NULL;
        END;
      ELSE
        --Air-conn serial# but no model inf 
        IF (SUBSTR(v_serial_no,5,1) = 'A') AND (SUBSTR(v_serial_no,4,1)!= 'T') THEN
              v_return.flag        := 'A';
        END IF;
      END IF;
      
      --------------------------GATHERING THE REQUIRED INFO------------------------------------------- 
      -- Validating & grathering end_user cell#
      IF (v_end_user_cell IS NOT NULL) THEN
        IF (LENGTH(TRIM(TRANSLATE(v_end_user_cell, ' +-.0123456789', ' '))) IS NOT NULL) THEN
          v_return.end_user_cell   := null;
        ELSE
          IF LENGTH(v_end_user_cell) IN (9,10,11,12) THEN
            SELECT                    DECODE(SUBSTR(v_end_user_cell,1,2), '84', v_end_user_cell
                                            ,'84'|| DECODE( SUBSTR(v_end_user_cell,1,1)
                                            ,'0'
                                            ,SUBSTR(v_end_user_cell, 2, LENGTH(v_end_user_cell)-1)
                                            ,v_end_user_cell))
            INTO                      v_return.end_user_cell              
            FROM                      DUAL;
           
          ELSE
            v_return.end_user_cell := null;
          END IF; 
        END IF;    
      END IF;
      --Gathering serial# & model
      v_return.serial_no           := v_serial_no;
      v_return.model               := v_model;
    END IF;
        
    RETURN                            v_return;
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'FN_MSGBODY_SIMULATE' , DBMS_UTILITY.format_error_backtrace|| '/'||SQLERRM 
                                          ,p_msgbody);
      
      v_return.flag                := 'F';
      RETURN                          v_return;
  END FN_MSGBODY_SIMULATE;
  ------------------------------------------------------------------------------------------
  --                    FUNCTION GET MT RESPONSE FROM MO BY RESP_TYPE & MT_SUB_MSG                  
  ------------------------------------------------------------------------------------------    
  FUNCTION FN_GET_MT_RESPONSE(
           p_resp_type                VARCHAR2
          ,p_mt_sub_msg               VARCHAR2)
  RETURN                              VARCHAR2
  ------------------------------------------------------------------------------------------
  IS 
           v_return                   VARCHAR2(200) := null;
  ------------------------------------------------------------------------------------------
  BEGIN
    SELECT                            gabit_id
                                   || p_mt_sub_msg 
    INTO                              v_return
    FROM                              TB_MT_SMS_RESP_MSG
    WHERE  resp_type                = p_resp_type;
        
    RETURN                            v_return;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      PR_RAISE_ERR( 'FN_GET_MT_RESPONSE' ,DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                         ,p_resp_type || '/'||p_mt_sub_msg||'/'||v_return);
      
      RETURN p_mt_sub_msg;  
  END FN_GET_MT_RESPONSE; 
  ------------------------------------------------------------------------------------------
  --                    FUNCTION GETTING WARRANTY RESPONSE
  ------------------------------------------------------------------------------------------
  FUNCTION FN_GET_WT_RESPONSE( 
           p_serial_no                VARCHAR2
          ,p_model                    VARCHAR2)
  RETURN                              WT_RESP_TYPE
  ------------------------------------------------------------------------------------------
  IS
           v_return                   WT_RESP_TYPE := WT_RESP_TYPE(null, null, null);
  ------------------------------------------------------------------------------------------
  BEGIN
    SELECT                            '$' || model || ', Serial/IMEI ' || serial_no 
                                   || '$' || TO_CHAR(wt_start_date,'DD/MM/YYYY')
                                     , (wt_end_date - wt_start_date)/(365-1)    
                                     , 'O' 
    INTO                              v_return.wt_resp_msg
                                     ,v_return.wt_resp_year
                                     ,v_return.wt_resp_flag 
    FROM  (
           SELECT  DISTINCT(model)    MODEL
                  ,serial_no          SERIAL_NO
                  ,MIN(wt_start_date) WT_START_DATE 
                  ,MIN(wt_end_date)   WT_END_DATE
           FROM  (       
                   SELECT             model
                                     ,serial_no
                                     ,wt_start_date
                                     ,wt_end_date
                   FROM               TB_SN_SO_WT_MST
              UNION   
                   SELECT             model
                                     ,serial_no
                                     ,wt_start_date
                                     ,wt_end_date
                   FROM               TB_SN_SO_WT_MST0)
           GROUP BY
                                      model
                                     ,serial_no)
    WHERE  serial_no                = p_serial_no
    AND    model                 LIKE DECODE(p_model, null, '%', p_model);
        
    RETURN v_return;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_return.wt_resp_flag        := 'F';  
      RETURN                        v_return;
          
    WHEN TOO_MANY_ROWS THEN
      v_return.wt_resp_flag        := 'M'; 
      RETURN                        v_return;
          
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'FN_GET_WT_RESPONSE' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                         ,p_serial_no || p_model);
      
      v_return.wt_resp_flag        := 'F';
      RETURN                       v_return;    
  END FN_GET_WT_RESPONSE; 
  -----------------------------------------------------------------------------------------
  /*
  FUNCTION FN_GET_WT_RESPONSE( 
           p_serial_no                VARCHAR2
          ,p_model                    VARCHAR2)
  RETURN                              VARCHAR2
  ------------------------------------------------------------------------------------------
  IS
           v_return                   VARCHAR2(160) := null;
  ------------------------------------------------------------------------------------------
  BEGIN
    SELECT                            '$' || model || ', Serial/IMEI ' || serial_no 
                                          || '$' || TO_CHAR(wt_start_date,'DD/MM/YYYY')
                                          || '$' || TO_CHAR(wt_end_date, 'DD/MM/YYYY')
    INTO                              v_return
    FROM  (
           SELECT  DISTINCT(model)    MODEL
                  ,serial_no          SERIAL_NO
                  ,MIN(wt_start_date) WT_START_DATE 
                  ,MIN(wt_end_date)   WT_END_DATE
           FROM  (       
                   SELECT             model
                                     ,serial_no
                                     ,wt_start_date
                                     ,wt_end_date
                   FROM               TB_SN_SO_WT_MST
              UNION   
                   SELECT             model
                                     ,serial_no
                                     ,wt_start_date
                                     ,wt_end_date
                   FROM               TB_SN_SO_WT_MST0)
           GROUP BY
                                      model
                                     ,serial_no)
    WHERE  serial_no                = p_serial_no
    AND    model                 LIKE DECODE(p_model, null, '%', p_model);
        
    RETURN v_return;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN                          'F';
          
    WHEN TOO_MANY_ROWS THEN
      RETURN                          'M';
          
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'FN_GET_WT_RESPONSE' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                         ,p_serial_no || p_model);
      
      RETURN                          'F';    
  END FN_GET_WT_RESPONSE; */
  ------------------------------------------------------------------------------------------
  ----                  FUNCTION VALIDATE SN (MUST IN CHAR OR NUMBERIC)
  ------------------------------------------------------------------------------------------
  FUNCTION FN_SN_VALIDATE( 
           p_serial_no                VARCHAR2)
  RETURN                              BOOLEAN
  ------------------------------------------------------------------------------------------
  IS
           v_prefix                   NUMBER  (3) := 0;
           v_sctcodes                 VARCHAR2(4) := null; 
           v_last3dgs                 VARCHAR2(3) := null;
           i_cnt                      NUMBER  (2) := 0;   
           v_return                   BOOLEAN     := true;
  ------------------------------------------------------------------------------------------
  BEGIN
    IF LENGTH(p_serial_no)     NOT IN (12,13,15) THEN
       v_return                    := FALSE;
           
       RETURN                         v_return;
    END IF;
    
    FOR i_cnt IN 1 .. LENGTH(p_serial_no)
      LOOP
        IF SUBSTR(p_serial_no,i_cnt,1) NOT IN ('0','1','2','3','4','5','6','7','8','9',
                   'Q','W','E','R','T','Y','U','I','O','P','A','S','D','F','G','H','J',
                   'K','L','Z','X','C','V','B','N','M')  THEN
           v_return              := FALSE;
               
           RETURN                   v_return;
        END IF;
      END LOOP;
    
    IF LENGTH(p_serial_no) IN (12,13) THEN
      v_prefix                       := SUBSTR(p_serial_no, 1, 3);
      v_sctcodes                     := UPPER(SUBSTR(p_serial_no, 4, 4));
      v_last3dgs                     := SUBSTR(p_serial_no, -3, 3);
      
      IF (LENGTH(TRIM(TRANSLATE(v_prefix,    ' +-.0123456789', ' '))) IS NOT NULL)
      OR (LENGTH(TRIM(TRANSLATE(v_last3dgs , ' +-.0123456789', ' '))) IS NOT NULL) 
      OR (LENGTH(TRIM(TRANSLATE(v_sctcodes , ' +-.0123456789', ' ')))        != 4)  THEN
        v_return                    := FALSE;
             
        RETURN                         v_return;
      END IF;  
      IF SUBSTR(v_sctcodes,1,1) NOT IN ('V','T','K','I','H','P') THEN
        v_return                    := FALSE;
        
        RETURN v_return;
      END IF;
        
      IF (TO_NUMBER(SUBSTR(v_prefix,1,1)) > 9) OR (TO_NUMBER(SUBSTR(v_prefix,2,2)) > 12) THEN
        v_return                    := FALSE;
          
        RETURN                         v_return;
      END IF;
    END IF;
      
    RETURN                          v_return;
  ------------------------------------------------------------------------------------------
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'FN_SN_VALIDATE' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                     ,p_serial_no);
          
      RETURN                         FALSE;
  END FN_SN_VALIDATE;
  ------------------------------------------------------------------------------------------
  ---                   FUNCTION CHECK SN DUPLICATION
  ------------------------------------------------------------------------------------------
  FUNCTION FN_SN_DUP_CHECK(
           p_serial_no                    VARCHAR2
          ,p_model                        VARCHAR2
          ,p_dup_type                     VARCHAR2
          ,p_cmdcode                      VARCHAR2) 
  RETURN                                  SN_DUP_TYPE 
  ------------------------------------------------------------------------------------------
  IS
   -- v_model                               VARCHAR2(20):= null;
    v_return                              SN_DUP_TYPE := SN_DUP_TYPE (null, null, null);
  ------------------------------------------------------------------------------------------
  BEGIN
    IF p_dup_type                       = const_so_sn_inc_dup THEN
      SELECT                              'D' 
                                         ,'$' || model || ', Serial ' || serial_no 
                                        ||'$' || DECODE(shop_cell, null, shop_code, shop_cell) 
                                        ||'$' || TO_CHAR(sellout_date,'DD/MM/YYYY')
                                         ,model
      INTO                                v_return.dup_flag
                                         ,v_return.resp_msg
                                         ,v_return.model
      FROM                                TB_SN_SO_WT_MST
      WHERE  serial_no                  = p_serial_no
      AND    model                   LIKE DECODE(p_model, null, '%', p_model);
    ELSIF p_dup_type                    = const_sn_isl_dup THEN
      SELECT                              'D'
                                         ,'$' || model || ',Serial ' || serial_no
                                        ||'$' || DECODE(isl_cell, null, isl_code, isl_cell)  
                                        ||'$' || TO_CHAR(isl_date,'DD/MM/YYYY')
                                         ,model
      INTO                                v_return.DUP_FLAG
                                         ,v_return.RESP_MSG
                                         ,v_return.MODEL  
      FROM                                TB_SN_INSTALLER
      WHERE serial_no                   = p_serial_no
      AND    model                   LIKE DECODE(p_model, null, '%', p_model);                              
    ELSE
      SELECT                              'D'
                                         ,'$' || serial_no
                                        ||'$' || shop_cell
                                        ||'$' || TO_CHAR(receive_date,'DD/MM/YYYY')
                                         ,model  
      INTO                                v_return.dup_flag
                                         ,v_return.resp_msg
                                         ,v_return.model
      FROM                                TB_SN_PND_HIST
      WHERE  serial_no                  = p_serial_no
      AND    'ABN'||model            LIKE DECODE(p_model, null, '%', '%'||p_model)
      AND    cmdcode                    = p_cmdcode
      AND    pnd_type                   = p_dup_type
      AND    cfm_flag                   = 'N';
    END IF;
      
    RETURN                                v_return;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_return.dup_flag                := 'O';
          
      RETURN                              v_return;
    WHEN TOO_MANY_ROWS THEN
      v_return.dup_flag                := 'M';
         
      RETURN                              v_return;
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'FN_SO_SN_DUP_CHECK' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                         ,p_serial_no || p_model);
      
      v_return.dup_flag := 'F';
      RETURN v_return;
  END FN_SN_DUP_CHECK;
  ------------------------------------------------------------------------------------------
  ---                   MAJOR FUNCTION :VERIFY SERIAL# IN THE STORE
  ------------------------------------------------------------------------------------------
  FUNCTION FN_SN_VERIFY(
           p_serial_no                       VARCHAR2
          ,p_model                           VARCHAR2)
  RETURN                                     SN_VRF_TYPE 
  ------------------------------------------------------------------------------------------
  IS
           v_sub_sn_no                       VARCHAR2(10);
  
           v_return                          SN_VRF_TYPE := SN_VRF_TYPE(null, null, null, null);
  ------------------------------------------------------------------------------------------
  BEGIN
    IF p_model                            IS NULL THEN
      SELECT                                 'O'
                                            ,LTRIM(RTRIM(model))
                                            ,suffix
                                            ,out_date
      INTO                                   v_return.vrf_flag
                                            ,v_return.model
                                            ,v_return.suffix
                                            ,v_return.sellin_date
      FROM   VW_SN_NM
      WHERE  serial_no                     = p_serial_no
      AND    out_date                      =(SELECT MAX(out_date)
                                             FROM   VW_SN_NM
                                             WHERE  serial_no                 = p_serial_no);
    ELSE  
      SELECT                                 'O'
                                            ,LTRIM(RTRIM(model))
                                            ,suffix
                                            ,out_date
      INTO                                   v_return.vrf_flag
                                            ,v_return.model
                                            ,v_return.suffix
                                            ,v_return.sellin_date
      FROM   VW_SN_NM
      WHERE  serial_no                     = p_serial_no
      AND    LTRIM(LTRIM(model))           = p_model
      AND    out_date                      =(SELECT MAX(out_date)
                                             FROM   VW_SN_NM
                                             WHERE  serial_no                 = p_serial_no
                                             AND    RTRIM(LTRIM(model))       = p_model);
    END IF;
    
    RETURN                                   v_return;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      /*v_return.vrf_flag                   := 'F';
             
      RETURN                                 v_return;  */ 
      BEGIN
        v_sub_sn_no                       := SUBSTR(p_serial_no, 1,3) 
                                          || SUBSTR(p_serial_no, 8, LENGTH(p_serial_no)-7);
        IF p_model                        IS NULL THEN
          SELECT                             'O'
                                            ,LTRIM(RTRIM(model))
                                            ,suffix
                                            ,out_date
          INTO                              v_return.vrf_flag
                                           ,v_return.model
                                           ,v_return.suffix
                                           ,v_return.sellin_date
          FROM   VW_SN_89
          WHERE  serial_no                = v_sub_sn_no;
        ELSE
          SELECT                             'O'
                                            ,LTRIM(RTRIM(model))
                                            ,suffix
                                            ,out_date
          INTO                               v_return.vrf_flag
                                            ,v_return.model
                                            ,v_return.suffix
                                            ,v_return.sellin_date
          FROM   VW_SN_89
          WHERE  serial_no                 = v_sub_sn_no  
          AND    LTRIM(RTRIM(model))       = p_model
          AND    out_date                  =(SELECT MAX(out_date)
                                             FROM   VW_SN_89
                                             WHERE  serial_no                 = v_sub_sn_no
                                             AND    LTRIM(RTRIM(model))       = p_model   );
        END IF;
                                            
        RETURN                               v_return;
      EXCEPTION
        WHEN VALUE_ERROR THEN
          v_return.vrf_flag               := 'F';
             
          RETURN                             v_return;
        WHEN NO_DATA_FOUND THEN
          v_return.vrf_flag               := 'F';
             
          RETURN                             v_return;
        WHEN TOO_MANY_ROWS THEN
          v_return.vrf_flag               := 'M';
              
          RETURN                             v_return;  
        WHEN OTHERS THEN
          PR_RAISE_ERR('FN_SN_VERIFY',DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                     ,p_serial_no || p_model);
              
          v_return.vrf_flag               := 'F'; 
          RETURN                             v_return;
      END;
  ------------------------------------------------------------------------------------------  
    WHEN TOO_MANY_ROWS THEN
      v_return.vrf_flag                  := 'M';
       
      RETURN                                v_return;
    WHEN OTHERS THEN
      PR_RAISE_ERR('FN_SN_VERIFY',DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                 ,p_serial_no || p_model);
      
      v_return.vrf_flag                   := 'F'; 
      RETURN                                 v_return;
  END FN_SN_VERIFY;
  ------------------------------------------------------------------------------------------
  ---                   MAJOR FUNCTION :VERIFY SERIAL# IN THE STORE FOR INSTALLER
  ------------------------------------------------------------------------------------------
  FUNCTION FN_SN_VERIFY_ISL(
           p_serial_no                       VARCHAR2
          ,p_model                           VARCHAR2)
  RETURN                                     SN_VRF_TYPE 
  ------------------------------------------------------------------------------------------
  IS
          -- v_sub_sn_no                       VARCHAR2(10);
           v_return                          SN_VRF_TYPE := SN_VRF_TYPE(null, null, null, null);
  ------------------------------------------------------------------------------------------
  BEGIN
    SELECT                                 'O'
                                          ,LTRIM(RTRIM(model))
                                          ,suffix
                                          ,out_date
    INTO                                   v_return.vrf_flag
                                          ,v_return.model
                                          ,v_return.suffix
                                          ,v_return.sellin_date
    FROM   VW_SN_ALL
    WHERE  serial_no                     = p_serial_no
    AND    LTRIM(LTRIM(model))           = p_model
    AND    out_date                      =(SELECT MAX(out_date)
                                           FROM   VW_SN_ALL
                                           WHERE  serial_no                 = p_serial_no
                                           AND    RTRIM(LTRIM(model))       = p_model);
    RETURN                                   v_return;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_return.vrf_flag                   := 'F';
             
      RETURN                                 v_return;   
      /*BEGIN
        v_sub_sn_no                       := SUBSTR(p_serial_no, 1,3) 
                                          || SUBSTR(p_serial_no, 8, LENGTH(p_serial_no)-7);
        IF p_model                        IS NULL THEN
          SELECT                             'O'
                                            ,LTRIM(RTRIM(model))
                                            ,suffix
                                            ,out_date
          INTO                              v_return.vrf_flag
                                           ,v_return.model
                                           ,v_return.suffix
                                           ,v_return.sellin_date
          FROM   VW_SN_89
          WHERE  serial_no                = v_sub_sn_no;
        ELSE
          SELECT                             'O'
                                            ,LTRIM(RTRIM(model))
                                            ,suffix
                                            ,out_date
          INTO                               v_return.vrf_flag
                                            ,v_return.model
                                            ,v_return.suffix
                                            ,v_return.sellin_date
          FROM   VW_SN_89
          WHERE  serial_no                 = v_sub_sn_no  
          AND    LTRIM(RTRIM(model))       = p_model
          AND    out_date                  =(SELECT MAX(out_date)
                                             FROM   VW_SN_89
                                             WHERE  serial_no                 = v_sub_sn_no
                                             AND    LTRIM(RTRIM(model))       = p_model   );
        END IF;
                                            
        RETURN                               v_return;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_return.vrf_flag               := 'F';
             
          RETURN                             v_return;
        WHEN TOO_MANY_ROWS THEN
          v_return.vrf_flag               := 'M';
              
          RETURN                             v_return;  
        WHEN OTHERS THEN
          PR_RAISE_ERR('FN_SN_VERIFY_ISL',DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                     ,p_serial_no || p_model);
              
          v_return.vrf_flag               := 'F'; 
          RETURN                             v_return;*/
      /*END;*/
  ------------------------------------------------------------------------------------------  
    WHEN TOO_MANY_ROWS THEN
      v_return.vrf_flag                  := 'M';
       
      RETURN                                v_return;
    WHEN OTHERS THEN
      PR_RAISE_ERR('FN_SN_VERIFY_ISL',DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                 ,p_serial_no || p_model);
      
      v_return.vrf_flag                   := 'F'; 
      RETURN                                 v_return;
  END FN_SN_VERIFY_ISL;
  
  ------------------------------------------------------------------------------------------
  --                    FUNCTION          : PRODUCTION SERIAL CHECK 
  ------------------------------------------------------------------------------------------
  FUNCTION FN_SN_PROD_CHK(
           p_serial_no                      VARCHAR2)
  RETURN                                    VARCHAR2
  ------------------------------------------------------------------------------------------
  IS
    v_return                                VARCHAR2(01);
  ------------------------------------------------------------------------------------------                                          
  BEGIN
    SELECT                                  'T'
    INTO                                    v_return
    FROM (SELECT customer_model_serial_no   SERIAL_NO
          FROM   XXOWMM_MES_SN2_S_IF@DBL_MES
          WHERE  customer_model_serial_no = p_serial_no
        UNION
          SELECT DISTINCT(a.serial_no)      SERIAL_NO
          FROM LKALSMGR.TBL_SERIAL_NO_PRINTED_SERIAL@DBL_MES A,
              (SELECT work_order, serial_no 
               FROM LKALSMGR.TBL_SERIAL_NO_PRINTED_SERIAL@DBL_MES
               WHERE serial_no  = p_serial_no)               B
          WHERE  a.work_order   = b.work_order
          AND    a.serial_no    = b.serial_no
          AND    a.serial_no_usage LIKE 'DISPLAY_SET%'
        UNION
          SELECT lot_set                    SERIAL_NO                 
          FROM   TB_WP_ADDWMSINPLOG@DBL_MES
          WHERE  actualtime = (SELECT max(actualtime)
                               FROM   TB_WP_ADDWMSINPLOG@DBL_MES
                               WHERE  lot_set = p_serial_no)
          AND    lot_set = p_serial_no
          AND    comm1          != '*MANUAL*'
        UNION
          SELECT DISTINCT(label_sn)
          FROM   lkalsmgr.set_label_log_detail_v_kals@DBL_MES
          WHERE  label_sn       = p_serial_no
        UNION
          SELECT lot_set                    SERIAL_NO
          FROM   VNXX_SN_CBU_HIST_IF
          WHERE  lot_set        = p_serial_no);
  
    RETURN   v_return;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_return     := 'F';
      RETURN       v_return;
      
    WHEN TOO_MANY_ROWS THEN
      v_return     := 'T';
      RETURN       v_return;
      
    WHEN OTHERS THEN
      
      /*v_return     := 'T';
      RETURN       v_return;
      */
      PR_RAISE_ERR('FN_SN_PROD_CHK',DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                   ,p_serial_no);
      
      v_return     := 'F'; 
      RETURN       v_return;        
  END;    
  ------------------------------------------------------------------------------------------
  --                    FUNCTION          : SMS TRANSLATION 
  ------------------------------------------------------------------------------------------
  FUNCTION FN_SMS_TRANS(
           p_sms                            VARCHAR2)
  RETURN                                    VARCHAR2
  ------------------------------------------------------------------------------------------
  IS
    v_template_id                           VARCHAR2(04)  := null;
    v_$num                                  NUMBER  (02)  :=    0;
    v_$seq                                  NUMBER  (01)  :=    1;
    v_sms_sub                               VARCHAR2(160) := RTRIM(LTRIM(p_sms));
  
    v_return                                VARCHAR2(160) := p_sms;
  ------------------------------------------------------------------------------------------
  BEGIN
    v_template_id                        := SUBSTR(v_sms_sub, 1, INSTR(v_sms_sub,'$',1) -1);
    
    SELECT                                  gabit_template
    INTO                                    v_return
    FROM                                    TB_MT_SMS_RESP_MSG
    WHERE                        gabit_id = v_template_id;
    
    v_sms_sub                            := REPLACE(v_sms_sub, v_template_id||'$','');
    
    v_$num                               := INSTR(v_sms_sub,'$', 1);
    IF v_$num                             = 0 THEN
     v_return                            := REPLACE(v_return, '{P1}', v_sms_sub);
    ELSE
      WHILE v_$num                        > 0 
        LOOP
        v_return                         := REPLACE(v_return, '{P'|| v_$seq ||'}', SUBSTR(v_sms_sub, 1, v_$num -1));
        v_sms_sub                        := REPLACE(v_sms_sub, SUBSTR(v_sms_sub, 1, v_$num), '');
        
        v_$num                           := INSTR(v_sms_sub,'$', 1);
        v_$seq                           := v_$seq +1;
      END LOOP;
      
      v_return                           := REPLACE(v_return, '{P'|| v_$seq ||'}', v_sms_sub);
    END IF;  
    
    RETURN                                  v_return;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN                                v_return;
    WHEN VALUE_ERROR THEN
      RETURN                                v_return;  
    WHEN OTHERS THEN
      PR_RAISE_ERR('FN_SMS_TRANS',DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                 ,v_sms_sub ||'/'|| v_template_id ); 
      RETURN                                v_return;
  END FN_SMS_TRANS;           
  ------------------------------------------------------------------------------------------
  --                    MAJOR PROCERDURE : UPDATE SN ONCE VERIFIED 
  ------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_SO_WT_MST(
            p_shop_code                     VARCHAR2             ,p_shop_cell                     VARCHAR2 
           ,p_serial_no                     VARCHAR2             ,p_model                         VARCHAR2
           ,p_suffix                        VARCHAR2             ,p_sellin_date                   DATE 
           ,p_sellout_date                  DATE                 ,p_sellout_time                  VARCHAR2 
           ,p_sellout_resp_type             VARCHAR2             ,p_sellout_resp_msg              VARCHAR2
           ,p_sms_yn                        VARCHAR2 DEFAULT 'Y' ,p_end_user_cell                 VARCHAR2
           ,p_incentive_cfm_flag            VARCHAR2 DEFAULT 'N' ,p_close_flag                    VARCHAR2 DEFAULT 'N') 
  ------------------------------------------------------------------------------------------
  IS  
    v_wt_start_date                         DATE        := p_sellout_date;
    v_wt_end_date                           DATE        := NULL;
    v_model_div                             VARCHAR2(10):= 'OTHERS'; 
    
    --v_scheme_id                             VARCHAR2(12):=null;
    v_shop_code                             VARCHAR2(08):=null;
   -- v_region                                VARCHAR2(02):=null;
    v_extend_wt_days                        VARCHAR2(03):=null;
  ------------------------------------------------------------------------------------------
  BEGIN
    BEGIN
      SELECT                                DISTINCT(prod_l1)
      INTO                                  v_model_div
      FROM                                  TB_CM_MODEL_CAT
      WHERE  model                        = p_model
      GROUP  BY                             prod_l1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_model_div                      := 'OTHERS';
      WHEN OTHERS THEN
        PR_RAISE_ERR('PR_UPDATE_SN_SO_WT_MST_MODEL' ,DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                             , p_model);    
    END;
     
    IF    v_model_div                    IN (const_wt_2ys, const_wt_2yrs) THEN
      v_wt_end_date                      := v_wt_start_date + (365-1) * 2;
      
    ELSIF (v_model_div                   IN (const_wt_wm_2yrs, const_wt_rf_2yrs, const_wt_tv_2yrs))
       AND (TO_CHAR(sysdate,'YYYYMMDD')  >= '20141001') THEN                                 
       v_wt_end_date                     := v_wt_start_date + (365-1) * 2;
       
    ELSIF (v_model_div                   IN (const_wt_ac_2yrs))
       AND (TO_CHAR(sysdate,'YYYYMMDD')  >= '20150201') THEN 
       v_wt_end_date                     := v_wt_start_date + (365-1) * 2;   
    
    ELSE
       BEGIN
         SELECT billto_code
               ,amt
         INTO  v_shop_code 
              ,v_extend_wt_days
         FROM   TB_CM_INCENTIVE_ADHOC_DETAIL
         WHERE  scheme_id   = (SELECT scheme_id
                               FROM  TB_CM_INCENTIVE_ADHOC
                               WHERE close_flag ='N' 
                               AND   TO_CHAR(start_date,'YYYYMMDD') <= TO_CHAR(p_sellout_date,'YYYYMMDD')
                               AND   TO_CHAR(end_date  ,'YYYYMMDD') >= TO_CHAR(p_sellout_date,'YYYYMMDD')
                               AND   purpose_type                    = 'W')
         AND   TRIM(model)        = p_model
         AND   TRIM(billto_code)  = p_shop_code;
         
         v_wt_end_date                   := v_wt_start_date + TO_NUMBER(v_extend_wt_days);  
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
            v_wt_end_date                := v_wt_start_date + (365-1);  
         WHEN OTHERS THEN
            PR_RAISE_ERR('PR_UPDATE_SN_SO_WT_MST_WT' ,DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                             ,p_serial_no || p_model); 
       END;                                       
    END IF;
    
    BEGIN
      INSERT INTO                           TB_SN_SO_WT_MST(
                                            shop_code           ,shop_cell
                                           ,serial_no           ,model
                                           ,suffix              ,sellin_date
                                           ,sellout_date        ,sellout_time
                                           ,wt_start_date       ,wt_end_date
                                           ,sellout_resp_type   ,sellout_resp_msg
                                           ,sms_yn              ,end_user_cell
                                           ,incentive_cfm_flag  ,close_flag)
      VALUES                                               (
                                            p_shop_code         ,p_shop_cell                            
                                           ,p_serial_no         ,p_model
                                           ,p_suffix            ,p_sellin_date
                                           ,p_sellout_date      ,p_sellout_time
                                           ,v_wt_start_date     ,v_wt_end_date
                                           ,p_sellout_resp_type ,p_sellout_resp_msg
                                           ,p_sms_yn            ,p_end_user_cell
                                           ,p_incentive_cfm_flag,p_close_flag );
      COMMIT;
                     
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        PR_RAISE_ERR('PR_UPDATE_SN_SO_WT_MST' ,DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                              ,p_serial_no || p_model);
        
        ROLLBACK; 
      WHEN OTHERS THEN
            PR_RAISE_ERR('PR_UPDATE_SN_SO_WT_MST_WT1' ,DBMS_UTILITY.format_error_backtrace || '/'|| SQLERRM 
                                             ,p_serial_no || p_model);            
    END;             
  END PR_UPDATE_SN_SO_WT_MST;
  ------------------------------------------------------------------------------------------
  --                    PROCEDURE UPDATE SERIAL_NO TO SO&WT HISTORY TABLE
  ------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_SN_SO_WT_HIST(
            p_serial_no               VARCHAR2              ,p_resp_type               VARCHAR2
           ,p_resp_msg                VARCHAR2              ,p_mo_msgbody              VARCHAR2
           ,p_receive_date            DATE                  ,p_shop_cell               VARCHAR2 DEFAULT null
           ,p_end_user_cell           VARCHAR2 DEFAULT null ,p_sms_yn                  VARCHAR2 DEFAULT 'Y'
           ,p_moseq                   VARCHAR2 DEFAULT 0    ,p_cmdcode                 VARCHAR2 DEFAULT const_cmdcode_so
           ,p_model                   VARCHAR2 DEFAULT null ,p_shop_code               VARCHAR2 DEFAULT null
           ,p_success_flag            VARCHAR2 DEFAULT 'N'  ,p_edi_file                VARCHAR2 DEFAULT null)
  ------------------------------------------------------------------------------------------
  IS
           p_edi_head                 VARCHAR2(10) := NULL;
  ------------------------------------------------------------------------------------------
  BEGIN
    IF      p_edi_file                IS NOT NULL THEN
            p_edi_head := SUBSTR(p_edi_file,1,INSTR(p_edi_file,'_')-1);
    END IF;  
    INSERT INTO                       TB_SN_SO_WT_HIST(
                                      serial_no        ,model
                                     ,end_user_cell    ,shop_code
                                     ,shop_cell        ,receive_date 
                                     ,moseq            ,cmdcode
                                     ,resp_type        ,resp_msg
                                     ,mo_msgbody       ,sms_yn
                                     ,success_flag     ,edi_file
                                     ,edi_head)
    VALUES                                          (
                                      p_serial_no      ,p_model
                                     ,p_end_user_cell  ,p_shop_code
                                     ,p_shop_cell      ,p_receive_date
                                     ,p_moseq          ,p_cmdcode
                                     ,p_resp_type      ,p_resp_msg
                                     ,p_mo_msgbody     ,p_sms_yn
                                     ,p_success_flag   ,p_edi_file
                                     ,p_edi_head);
    COMMIT;                          
  END PR_UPDATE_SN_SO_WT_HIST;      
  ------------------------------------------------------------------------------------------
  --                    PROCEDURE UPADTE SERIAL_NO TO PENDING TABLE
  ------------------------------------------------------------------------------------------
  PROCEDURE PR_UPDATE_PND_HIST( 
            p_serial_no                VARCHAR2             ,p_model                    VARCHAR2
           ,p_cmdcode                  VARCHAR2             ,p_moseq                    VARCHAR2
           ,p_shop_code                VARCHAR2             ,p_shop_cell                VARCHAR2
           ,p_end_user_cell            VARCHAR2             ,p_pnd_type                 VARCHAR2
           ,p_resp_type                VARCHAR2             ,p_resp_msg                 VARCHAR2
           ,p_mo_msgbody               VARCHAR2             ,p_receive_time             DATE
           ,p_sms_yn                   VARCHAR2 DEFAULT 'Y'   ,p_cfm_flag               VARCHAR2 DEFAULT 'N'
           ,p_cfm_user                 VARCHAR2 DEFAULT 'SYS' ,p_cfm_date               DATE     DEFAULT sysdate)
  ------------------------------------------------------------------------------------------
  IS
  ------------------------------------------------------------------------------------------
  BEGIN 
    INSERT INTO                        TB_SN_PND_HIST(
                                       serial_no         ,model
                                      ,cmdcode           ,moseq
                                      ,shop_code         ,shop_cell
                                      ,end_user_cell     ,pnd_type
                                      ,resp_type         ,resp_msg
                                      ,mo_msgbody        ,receive_date
                                      ,cfm_flag          ,cfm_user
                                      ,cfm_date          ,sms_yn)
    VALUES(
                                      p_serial_no        ,p_model
                                     ,p_cmdcode          ,p_moseq
                                     ,p_shop_code        ,p_shop_cell
                                     ,p_end_user_cell    ,p_pnd_type
                                     ,p_resp_type        ,p_resp_msg
                                     ,p_mo_msgbody       ,p_receive_time             
                                     ,p_cfm_flag         ,p_cfm_user
                                     ,p_cfm_date         ,p_sms_yn);
        
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'PR_UPDATE_PND_HIST' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                         ,p_serial_no ||'/'||p_model);
  END PR_UPDATE_PND_HIST;
  ------------------------------------------------------------------------------------------
  --                    PROCEDURE UPADTE SERIAL_NO TO INSTALLER
  ------------------------------------------------------------------------------------------ 
  PROCEDURE PR_UPDATE_ISL_HIST(
            p_serial_no                VARCHAR2, p_model                       VARCHAR2
           ,p_cmdcode                  VARCHAR2, p_moseq                       VARCHAR2
           ,p_isl_code                 VARCHAR2, p_isl_cell                    VARCHAR2
           ,p_end_user_cell            VARCHAR2, p_resp_type                   VARCHAR2
           ,p_resp_msg                 VARCHAR2, p_isl_date                    DATE)
  IS
  BEGIN
    INSERT INTO TB_SN_INSTALLER(
                isl_code               , isl_cell
               ,serial_no              , model
               ,isl_date               , resp_type
               ,resp_msg               , eu_cell                
               ,cmdcode                , moseq)
    VALUES     (p_isl_code             , p_isl_cell
               ,p_serial_no            , p_model
               ,p_isl_date             , p_resp_type
               ,p_resp_msg             , p_end_user_cell
               ,p_cmdcode              , p_moseq);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      PR_RAISE_ERR( 'PR_UPDATE_ISL_HIST' ,DBMS_UTILITY.format_error_backtrace || '/'||SQLERRM 
                                         ,p_serial_no ||'/'||p_model);                                                 
  END        PR_UPDATE_ISL_HIST;                                                                  
END PK_SMS_MST;
